package cn.com.nes.site.module.dao.monitor;

import cn.com.nes.common.em.platform.OpeMethodEnum;
import cn.com.nes.common.util.*;
import cn.com.nes.mybatis.agent.ope.entity.IsEquipmentInfo;
import cn.com.nes.mybatis.agent.ope.mapper.EquipmentMapper;
import cn.com.nes.mybatis.agent.system.entity.ProtocolDataItemAuth;
import cn.com.nes.mybatis.agent.system.mapper.ProtocolDataItemAuthMapper;
import cn.com.nes.site.entity.DataReq;
import cn.com.nes.site.entity.OtherData;
import cn.com.nes.site.entity.PageData;
import cn.com.nes.site.entity.iot.CommandParams;
import cn.com.nes.site.module.dao.BaseDaoImpl;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.map.MapBuilder;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.script.JavaScriptEngine;
import com.alibaba.fastjson2.JSON;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import com.alibaba.fastjson2.TypeReference;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Map.Entry;

import static cn.com.nes.common.util.Globals.bucketName_ctce;
import static cn.com.nes.common.util.Globals.bucketName_mtpf;

@Repository
public class DeviceDaoImpl extends BaseDaoImpl {

    @Resource
    private ProtocolDataItemAuthMapper protocolDataItemAuthMapper;

    @Resource
    private EquipmentMapper equipmentMapper;

    public PageData getEquipmentCount(DataReq req) {

        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("查询设备数量失败");

        // 传入行政区划
        String xzqhdm = (String) req.getReqData().get("xzqhdm");
        // 用户行政区划
        String userxzqhdm = req.getUinfo().getAreacode();

        if (StringUtils.isEmpty(xzqhdm)) {
            if (!StringUtils.isEmpty(userxzqhdm)) {
                xzqhdm = userxzqhdm;
            }
        }

        /**
         * is_equipment_info    //设备信息表
         * is_install_info  //安装信息表
         * is_device_protocol_info  //设备协议信息
         * t_sblx   //设备类型
         * is_dict_value_info   //数据字典
         * is_norm_data_b   //采集数据表（实时表B）
         * is_norm_data // 采集数据表（实时表）
         * is_user_info //用户信息
         * t_xzqh   //行政区划
         */
        Connection conn = null;
        try {
            conn = DBUtils.getJdbcForOpe();
            StringBuffer sb = new StringBuffer();
            sb.append(" select count(1) sl, ifnull(tx.xzqhmc, '未知地区') xzqhmc, tx.xzqhdm, tx.zxjd jd, tx.zxwd wd, tx.xzqhjb");
            sb.append("  from nes_ope_prod.is_equipment_info iei ");
            sb.append("           inner join nes_ope_prod.is_install_info iii on (iii.dd_id = iei.dd_id and iii.sfsc = 0)                                           ");
            sb.append("           left join nes_ope_prod.is_device_protocol_info b on b.protocol_id = iei.protocol_id                                               ");
            sb.append("           left join nes_ope_prod.t_sblx ts on (ts.sblx_id = iei.sblx_id and ts.sfsc = 0)                                                    ");
            sb.append("           left join nes_ope_prod.is_dict_value_info idvi on idvi.type_id = 'd_type_2' and idvi.dic_no = ts.sblx                             ");
            sb.append("           left join nes_ope_prod.is_norm_data_b ind on ind.dtu_id = iei.wx_id and ind.sb_id = iei.sb_id                                     ");
            sb.append("       left join nes_ope_prod.is_norm_data ind2 on ind2.dtu_id = iei.wx_id and ind2.sb_id = iei.sb_id                                        ");
            sb.append("       left join nes_ope_prod.is_user_info iui on iui.user_id = iei.create_oper                                                              ");
            String query = "iii.xzqh";
            //group by
            String query1 = "tx.xzqhdm";
            String xzqhjb = "0";
            if (!StringUtils.isEmpty(xzqhdm) && !"86".equals(xzqhdm)) {
                //                xzqhjb = XzqhUtil.getXzqhjb(conn, xzqhdm);
                //modify by zhouy
                // 当xzqhd为多个时，取最小的级别
                xzqhjb = xzqhdm.contains(";") ? XzqhUtil.getMinXzqhjb(conn, xzqhdm) : XzqhUtil.getXzqhjb(conn, xzqhdm);
                if (StringUtils.isNotEmpty(xzqhjb)) {
                    query = XzqhUtil.f_get_dwonQueryXzqh_report("iii.xzqh", xzqhjb);
                    query1 = XzqhUtil.f_get_dwonQueryXzqh_report("tx.xzqhdm", xzqhjb);
                }

            } else {
                query = "concat(substr(iii.xzqh,1,2),'')";
            }
            sb.append("       left join nes_ope_prod.t_xzqh tx on tx.xzqhdm = " + query);
            sb.append("       where iei.sfsc = 0 and iei.sfzj != 2  ");
            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, userxzqhdm, "iii.xzqh"));
            String companyType = req.getUinfo().getCompanyType();
            if (!Globals.DEMO_COMPANYID.equals(req.getUinfo().getCompanyId())) {//不是演示账号
                if ("01".equals(companyType)) {//厂商
                    sb.append(" and iei.manufacture_id= '").append(req.getUinfo().getNes_companyId()).append("' ");
                } else if ("02".equals(companyType)) {//代理商
                    sb.append(" and iei.agent_id= '").append(req.getUinfo().getCompanyId()).append("' ");
                } else {//管理员
                    sb.append(" and (ind.is_online = 0 and ind2.gmt_create>DATE_ADD(now(),INTERVAL -60 MINUTE)) and iei.manufacture_id='MA3NU7QX1' ");
                }
            } else {//演示账号默认德尚
                sb.append(" and (ind.is_online = 0 and ind2.gmt_create>DATE_ADD(now(),INTERVAL -60 MINUTE)) and iei.manufacture_id='MA3NU7QX1' ");
            }

            String ismap = (String) req.getReqData().get("ismap");
            if ("1".equals(ismap)) {
                sb.append(" and iii.jd is not null and  iii.wd is not null ");
            }

            String is_master = (String) req.getReqData().get("is_master");
            if (!StringUtils.isEmpty(is_master)) {
                if ("1".equals(is_master)) {
                    sb.append(" and iei.is_master= 1 ");
                } else {
                    sb.append(" and (iei.is_master is null or iei.is_master <> 1 ) ");
                }
            }

            if (!StringUtils.isEmpty((String) req.getReqData().get("xzqhdm")) && !"86".equals((String) req.getReqData()
                    .get("xzqhdm"))) {
                sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, (String) req.getReqData().get("xzqhdm"), "iii.xzqh"));
            }
            if (StringUtils.isNotEmpty(xzqhjb)) {
                sb.append(" and tx.xzqhjb >" + xzqhjb);
            }
            //如果加条件参考getEquipment方法

            sb.append(" group by " + query1);
            sb = DBUtils.sql_check(sb);
            List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
            //转换经纬度
            for (int i = 0; i < list.size(); i++) {
                List<Object> lnglat = new ArrayList<Object>();
                lnglat.add(list.get(i).get("jd"));
                lnglat.add(list.get(i).get("wd"));
                list.get(i).put("lnglat", lnglat);
            }
            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
        return res;
    }

    public PageData getEquipment(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("查询设备信息失败");
        Connection conn = null;
        try {
            conn = DBUtils.getJdbcForOpe();
            StringBuffer sb = new StringBuffer();
            StringBuffer sbfrom = new StringBuffer();
            sb.append("select iei.id id,iei.manufacture_id,imi.name manufactureName, iei.sjly,if(iei.sjly='ctce','清洁取暖',if(iei.sjly='ope','运营',if(iei.sjly='mtpf','厂商','热链云'))) sjlyName,iei.sb_id                                                                                  as sbId,         ");
            sb.append("              if(iei.zbq, datediff(iei.zbq, now()), 0)                                                   as sbxlh,             ");
            sb.append("              insert(iei.wx_id  , 2, 6, '******')                                                                                 as wxId,              ");
            sb.append("              iei.dd_id                                                                                  as ddId,              ");
            sb.append("              insert(iii.xmlxr , 2, 1, '**')                                                                                 as name,              ");
            sb.append("              insert(iii.xmlxrdh, 4, 4, '****')                                                                                as phone,             ");
            sb.append("              iii.xmdz                                                                                   as address,           ");
            sb.append("              idvi.title                                                                                 as sblx,              ");
            sb.append("              iei.sblx_id                                                                                as sblxId,            ");
            sb.append("              ts.sbxh,                                                                                                         ");
            sb.append("              if(ind.is_online = 0 and (ind.a350=0 or ind.a350=1 ) and ind2.gmt_create>DATE_ADD(now(),INTERVAL -60 MINUTE), '在线',if(ind.is_online is null,'未联网', '离线'))                                                          as isOnline,      ");
            sb.append("              if(ind.is_online = 0 and ind2.gmt_create>DATE_ADD(now(),INTERVAL -60 MINUTE) , if(ind.fault_code != '', '2', if(ind.is_warn = 1, '3', '1')), if(ind.is_online is null,'5', '4')) as state,       ");//1 正常，2故障，3预警，4离线
            sb.append("              if(ind.is_online = 0, if(ind.a350 = 0, '关机', if(ind.a350 =1,'开机','')), '')                                       onOff,         ");
            sb.append("              iui.xm                                                                                     as latestEditor,      ");
            sb.append("              date_format(iei.xgsj, '%Y-%m-%d %T')               as latestUpdateTime,  ");
            sb.append("              iei.alias,                                                                                                       ");
            sb.append("              iii.xzqh,                                                                                                        ");
            sb.append("              tx.xzqhmc,                                                                                                       ");
            //sb.append("              if(iuf.id is null, 0, 1)                                                                   as isFollow,          ");
            sb.append("              ind2.gmt_create                                                                            as cjsj,              ");
            sb.append("              iii.jd,                                                                                                          ");
            sb.append("              iii.wd,                                                                                                          ");
            sb.append("              ifnull(iii.azt,'')                                                                            azt,               ");
            sb.append("              ifnull(iii.pmt,'')                                                                            pmt,               ");
            sb.append("              ifnull(iii.sjt,'')                                                                            sjt,               ");
            sb.append("              ifnull(iii.images, ifnull(iiwoi.images,''))                                                                       images,            ");
            sb.append("              REPLACE(tx.xzqhqc, '->', '')                                                                  xzqhqc,            ");
            sb.append("              date_format(iei.zbq, '%Y-%m-%d')                                                              zbrq,              ");
            sb.append("              date_format(iii.cjsj, '%Y-%m-%d')                                                             installDate,       ");
            sb.append("              ts.sblxmc,                                                                                                       ");
            sb.append("              iei.sbxlh                                                                                     sbxlh,      ");
            //sb.append("              wxsb.wx_kh,                                                                                                      ");
            //sb.append("              date_format(wxsb.cjsj, '%Y-%m-%d')                                                            dtuRegDate,        ");
            sb.append("              b.`name`                                                                                      protocolName,      ");
            sb.append("              iei.protocol_id                                                                            as protocolId,        ");
            sb.append("              if(DATE_SUB(now(), INTERVAL 30 MINUTE) > ind2.gmt_create, 0, 1)                               devStatus,         ");
            sb.append("              if(ind2.is_online = 0, ind2.fault_codes, '')                                               as faultCodes,        ");
            sb.append("              iei.dealer_name                                                                            as dealerName,        ");
            sb.append("               iei.dealer_phone                                                                           as dealerPhone,       ");
            sb.append("             iui.user_id                                                                                as createUserId       ");
            sb.append("  from nes_ope_prod.is_equipment_info iei ");
            sb.append("           inner join nes_ope_prod.is_install_info iii on (iii.dd_id = iei.dd_id and iii.sfsc = 0)                                           ");
            sb.append("           left join nes_ope_prod.is_device_protocol_info b on b.protocol_id = iei.protocol_id                                               ");
            sb.append("           left join nes_ope_prod.t_sblx ts on (ts.sblx_id = iei.sblx_id and ts.sfsc = 0)                                                    ");
            sb.append("           left join nes_ope_prod.is_dict_value_info idvi on idvi.type_id = 'd_type_2' and idvi.dic_no = ts.sblx                             ");
            sb.append("           left join nes_ope_prod.is_norm_data_b ind on ind.dtu_id = iei.wx_id and ind.sb_id = iei.sb_id                                     ");
            sb.append("       left join nes_ope_prod.is_norm_data ind2 on ind2.dtu_id = iei.wx_id and ind2.sb_id = iei.sb_id                                        ");
            sb.append("       left join nes_ope_prod.is_user_info iui on iui.user_id = iei.create_oper                                                              ");
            sb.append("       left join nes_ope_prod.t_xzqh tx on tx.xzqhdm = iii.xzqh                                                                              ");
            sb.append("       left join nes_ope_prod.is_install_work_order_info iiwoi on (iei.dd_id = iiwoi.dd_id  and iei.sb_id = iiwoi.sb_id and iiwoi.sfsc = 0)       LEFT JOIN nes_ope_prod.is_manufacture_info imi ON iei.manufacture_id = imi.manufacture_id                        ");
            //sb.append("       left join nes_ope_prod.is_wxsb wxsb on iei.wx_id = wxsb.wx_id                                                                         ");
            //sb.append("       left join nes_ope_prod.is_user_follow iuf on iuf.userId = '0' and iuf.sbId = iei.sb_id                                          ");
            sb.append("       where iei.sfsc = 0                                                                                                       ");
            sb.append("         and iei.sfzj != 2                                                                                                      ");
            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo().getAreacode(), "iii.xzqh"));
            String companyType = req.getUinfo().getCompanyType();
            String manufactureId = (String) req.getReqData().get("manufactureId");
            if (StrUtil.isNotEmpty((String) req.getReqData().get("sbId"))) {
                //解决故障预警->故障列表详情数据为空  是否影响其他查询接口，暂时未发现
            } else if (!Globals.DEMO_COMPANYID.equals(req.getUinfo().getCompanyId())) {//不是演示账号
                if ("01".equals(companyType)) {//厂商
                    sb.append(" and iei.manufacture_id= '").append(req.getUinfo().getNes_companyId()).append("' ");
                } else if ("02".equals(companyType)) {//代理商
                    //                    sb.append(" and iei.agent_id= '").append(req.getUinfo().getCompanyId()).append("' ");
                    sb.append(" AND iei.manufacture_id in(select facturer_id from t_agent_facturer where agent_id= '")
                            .append(req.getUinfo().getCompanyId())
                            .append("' ) ");
                    sb.append(" and iei.agent_id like '%").append(req.getUinfo().getCompanyId()).append("%' ");
                } else {//管理员
                    //                    sb.append(" and (ind.is_online = 0 and ind2.gmt_create>DATE_ADD(now(),INTERVAL -60 MINUTE)) ");
                    //                    if (StringUtils.isEmpty(manufactureId)) {
                    //                        sb.append("and iei.manufacture_id='MA3NU7QX1' ");
                    //                    }
                }
            } else {//演示账号默认德尚
                sb.append(" and (ind.is_online = 0 and ind2.gmt_create>DATE_ADD(now(),INTERVAL -60 MINUTE)) and iei.manufacture_id='MA3NU7QX1' ");
            }

            //            if (StrUtil.isNotEmpty((String) req.getReqData().get("sbId"))) {
            //                //解决故障预警->故障列表详情数据为空  是否影响其他查询接口，暂时未发现
            //            } else if (!Globals.DEMO_COMPANYID.equals(req.getUinfo().getCompanyId())) {//不是演示账号
            //                if ("01".equals(companyType)) {//厂商
            //                    sb.append(" and iei.manufacture_id= '").append(req.getUinfo().getNes_companyId()).append("' ");
            //                } else if ("02".equals(companyType)) {//代理商
            //                    sb.append(" and iei.agent_id= '").append(req.getUinfo().getCompanyId()).append("' ");
            //                }
            //            }

            String ismap = (String) req.getReqData().get("ismap");
            if ("1".equals(ismap)) {
                sb.append(" and iii.jd is not null and  iii.wd is not null ");
            }

            if (!StringUtils.isEmpty(manufactureId)) {
                sb.append("and iei.manufacture_id = '").append(manufactureId).append("'");
            }

            String is_master = (String) req.getReqData().get("is_master");
            if (!StringUtils.isEmpty(is_master)) {
                if ("1".equals(is_master)) {
                    sb.append(" and iei.is_master= 1 ");
                } else {
                    sb.append(" and (iei.is_master is null or iei.is_master <> 1 ) ");
                }
            }

            String state = (String) req.getReqData().get("state");

            ////1 正常（绿），2故障（红），3预警（橙），4离线 （灰）
            if (!StringUtils.isEmpty(state)) {
                switch (state) {
                    case "4"://离线 （有绑定dtu，一小时内有数,）
                        //sb.append(" and not (ind.is_online = 0 and (ind.a350=0 or ind.a350=1 ) and ind2.gmt_create>DATE_ADD(now(),INTERVAL -60 MINUTE)) ");
                        sb.append(" and (ind.is_online = 1 or (ind.is_online = 0 and ind2.gmt_create<=DATE_ADD(now(),INTERVAL -60 MINUTE)) or ind.is_online = 0 and ind.a350 is null and ind2.gmt_create>DATE_ADD(now(),INTERVAL -60 MINUTE) )");
                        break;
                    case "3"://预警
                        sb.append(" and (ind.is_online = 0 and ind2.gmt_create>DATE_ADD(now(),INTERVAL -60 MINUTE) and ind.is_warn = 1) ");
                        break;
                    case "2"://故障
                        sb.append(" and (ind.is_online = 0 and ind2.gmt_create>DATE_ADD(now(),INTERVAL -60 MINUTE) and ind.fault_code != '') ");
                        break;
                    case "1"://在线 (1小时内有数据，并且采集开关机为关机或者开机)
//                        sb.append(" and (ind.is_online = 0 and (ind.a350=0 or ind.a350=1 ) and ind2.gmt_create>DATE_ADD(now(),INTERVAL -60 MINUTE))  ");
                        sb.append(" and (ind.is_online = 0 and (ind.a350 is not null ) and ind2.gmt_create>DATE_ADD(now(),INTERVAL -60 MINUTE))  ");
                        break;
                    case "5":
                        sb.append(" and (ind.is_online is null)");
                        break;
                    default:
                        break;
                }
            }

            if (!StringUtils.isEmpty((String) req.getReqData().get("xzqhdm")) && !"86".equals((String) req.getReqData()
                    .get("xzqhdm"))) {
                sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, (String) req.getReqData().get("xzqhdm"), "iii.xzqh"));
            }

            if (!StringUtils.isEmpty((String) req.getReqData().get("cjsj_min"))) {
                sb.append(" and iii.cjsj >='").append(req.getReqData().get("cjsj_min")).append("' ");
            }

            if (!StringUtils.isEmpty((String) req.getReqData().get("cjsj_max"))) {
                sb.append(" and iii.cjsj <='").append(req.getReqData().get("cjsj_max")).append("' ");
            }

            if ("1".equals((String) req.getReqData().get("sfgb"))) {
                sb.append(" and iei.zbq<now() ");
            }

            String name = (String) req.getReqData().get("name");
            if (!StringUtils.isEmpty(name)) {
                sb.append(" and (iii.xmlxr like concat('%','")
                        .append(name)
                        .append("', '%') or iii.xmlxrdh like concat('%','")
                        .append(name)
                        .append("', '%') ) ");
            }
            if (!StringUtils.isEmpty((String) req.getReqData().get("phone"))) {
                sb.append(" and iii.xmlxrdh like concat('%','")
                        .append(req.getReqData().get("phone"))
                        .append("', '%') ");
            }
            if (!StringUtils.isEmpty((String) req.getReqData().get("address"))) {
                sb.append(" and iii.xmdz like concat('%','").append(req.getReqData().get("address")).append("', '%') ");
            }
            if (!StringUtils.isEmpty((String) req.getReqData().get("sbId"))) {
                sb.append(" and iei.sb_id ='").append(req.getReqData().get("sbId")).append("' ");
            }
            if (!StringUtils.isEmpty((String) req.getReqData().get("wxId"))) {
                sb.append(" and iei.wx_id like concat('%','").append(req.getReqData().get("wxId")).append("', '%') ");
            }
            sb.append(" and (iei.sjly='ctce' or iei.sjly='ope' or iei.sjly='mtpf' or iei.sjly='agent' )");
            if (!StringUtils.isEmpty((String) req.getReqData().get("sjly"))) {
                sb.append(" and iei.sjly ='").append(req.getReqData().get("sjly")).append("' ");
            }

            // 安装时间
            String installDate = (String) req.getReqData().get("installDate");
            if (StrUtil.isNotBlank(installDate)) {
                String installStartTime = installDate + " 00:00:00";
                String installEndTime = installDate + " 23:59:59";
                sb.append(" and (iii.cjsj >='")
                        .append(installStartTime)
                        .append("' and iii.cjsj <='")
                        .append(installEndTime)
                        .append("')");
            }

            //			sb.append("and iei.v1 = (select max(iei2.v1) from nes_ope_prod.is_equipment_info iei2 where iei.sb_id = iei2.sb_id)");
            sb = DBUtils.sql_check(sb);
            sbfrom.append(sb.toString());
            int idx = sbfrom.indexOf(" from ");
            sbfrom.delete(0, idx);
            sb.append(" order by iii.xgsj desc ");
            Page page = new Page(conn, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, new Object[]{});
            sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
            List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
            if (StrUtil.isNotEmpty((String) req.getReqData().get("sbId"))) {
                Map<String, Object> stringObjectMap = list.get(0);
                String azts = String.valueOf(stringObjectMap.get("azt"));
                String sjt = String.valueOf(stringObjectMap.get("sjt"));
                String pmt = String.valueOf(stringObjectMap.get("pmt"));
                String images = String.valueOf(stringObjectMap.get("images"));
                ArrayList aztList = new ArrayList();
                ArrayList imageList = new ArrayList();

                String sjly = String.valueOf(stringObjectMap.get("sjly"));
                if ("ctce".equals(sjly)) {
                    if (StrUtil.isNotEmpty(azts)) {
                        String[] azt = azts.split(";");
                        for (String i : azt) {
                            aztList.add(OssUtil.getImgSignUrlWithWaterMark(i, 3, Globals.bucketName_ctce, "清洁取暖工程管理"));
                        }
                    }
                    stringObjectMap.put("azt", aztList);
                    if (StrUtil.isNotEmpty(images)) {
                        String[] image = images.split(",");
                        for (String i : image) {
                            imageList.add(OssUtil.getImgSignUrlWithWaterMark(i, 4, Globals.bucketName_mtpf, "清洁取暖工程管理"));
                        }
                    }
                    stringObjectMap.put("images", imageList);
                } else if ("agent".equals(sjly)) {
                    if (StrUtil.isNotEmpty(azts)) {
                        String[] azt = azts.split(";");
                        for (String i : azt) {
                            aztList.add(OssUtil.getImgSignURL(i, 0, Globals.bucketName));
                        }
                    }
                    if (StrUtil.isNotEmpty(pmt)) {
                        aztList.add(OssUtil.getImgSignURL(pmt, 0, Globals.bucketName));
                    }
                    if (StrUtil.isNotEmpty(sjt)) {
                        aztList.add(OssUtil.getImgSignURL(sjt, 0, Globals.bucketName));
                    }
                    stringObjectMap.put("azt", aztList);
                    if (StrUtil.isNotEmpty(images)) {
                        String[] image = images.split(";");
                        for (String i : image) {
                            imageList.add(OssUtil.getImgSignURL(i, 0, Globals.bucketName));
                        }
                    }
                    stringObjectMap.put("images", imageList);
                } else {
                    if (StrUtil.isNotEmpty(azts)) {
                        String[] azt = azts.split(";");
                        for (String i : azt) {
                            aztList.add(OssUtil.getImgSignURL(i, 0, Globals.bucketName_ctce));
                        }
                    }
                    stringObjectMap.put("azt", aztList);
                    if (StrUtil.isNotEmpty(images)) {
                        String[] image = images.split(",");
                        for (String i : image) {
                            imageList.add(OssUtil.getImgSignURL(i, 0, Globals.bucketName_mtpf));
                        }
                    }
                    stringObjectMap.put("images", imageList);
                }
            }
            //转换经纬度
            for (int i = 0; i < list.size(); i++) {
                List<Object> lnglat = new ArrayList<Object>();
                lnglat.add(list.get(i).get("jd"));
                lnglat.add(list.get(i).get("wd"));
                list.get(i).put("lnglat", lnglat);
                list.get(i).put("style", Integer.parseInt((String) list.get(i).get("state")));
            }


            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
        return res;
    }


    public PageData getStation(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("查询设备信息失败");
        Connection conn = null;
        try {
            conn = DBUtils.getJNDIDBConn();
            StringBuffer sb = new StringBuffer();
            sb.append("SELECT  station_id,jd,wd,name,phone,address,xzqhdm from nes_ope_prod.is_repair_station_info a where (name!='' and name is not null) and jd is not null and wd is not null ");
            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo().getAreacode(), "a.xzqhdm"));
            String companyType = req.getUinfo().getCompanyType();

            if ("01".equals(companyType)) {//厂商
                sb.append(" and a.manufacture_id= '").append(req.getUinfo().getNes_companyId()).append("' ");
            } else if ("02".equals(companyType)) {////代理商
                sb.append(" and a.manufacture_id in (SELECT b.nes_companyId from t_agent_facturer a INNER JOIN t_company b on a.facturer_id=b.companyId and b.companyType='01' and  b.nes_companyId is not null where agent_id='")
                        .append(req.getUinfo().getCompanyId())
                        .append("') ");
            }

            if (!StringUtils.isEmpty((String) req.getReqData().get("xzqhdm")) && !"86".equals((String) req.getReqData()
                    .get("xzqhdm"))) {
                sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, (String) req.getReqData().get("xzqhdm"), "a.xzqhdm"));
            }

            sb.append(" order by a.name ");

            List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});

            //转换经纬度
            for (int i = 0; i < list.size(); i++) {
                List<Object> lnglat = new ArrayList<Object>();
                lnglat.add(list.get(i).get("jd"));
                lnglat.add(list.get(i).get("wd"));
                list.get(i).put("lnglat", lnglat);
            }
            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
        return res;
    }


    public PageData getRepairPeople(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("查询设备信息失败");
        Connection conn = null;
        try {
            conn = DBUtils.getJNDIDBConn();
            StringBuffer sb = new StringBuffer();
            sb.append("SELECT a.xm,a.ssxzqh,a.lxdh,a.jd,a.wd from nes_ope_prod.is_user_info a  where rylb_id='ryfl-04' and jd is not null and wd is not null and length(xm)>=4 ");
            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo().getAreacode(), "a.ssxzqh"));
            String companyType = req.getUinfo().getCompanyType();

            if ("01".equals(companyType)) {//厂商
                sb.append(" and a.manufacture_id= '").append(req.getUinfo().getNes_companyId()).append("' ");
            } else if ("02".equals(companyType)) {////代理商
                sb.append(" and a.manufacture_id in (SELECT b.nes_companyId from t_agent_facturer a INNER JOIN t_company b on a.facturer_id=b.companyId and b.companyType='01' and  b.nes_companyId is not null where agent_id='")
                        .append(req.getUinfo().getCompanyId())
                        .append("') ");
            }

            if (!StringUtils.isEmpty((String) req.getReqData().get("xzqhdm")) && !"86".equals((String) req.getReqData()
                    .get("xzqhdm"))) {
                sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, (String) req.getReqData().get("xzqhdm"), "a.ssxzqh"));
            }

            sb.append(" order by a.xm ");

            List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});

            //转换经纬度
            for (int i = 0; i < list.size(); i++) {
                List<Object> lnglat = new ArrayList<Object>();
                lnglat.add(list.get(i).get("jd"));
                lnglat.add(list.get(i).get("wd"));
                list.get(i).put("lnglat", lnglat);
            }
            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
        return res;
    }


    public PageData delEquipmentById(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("设备删除失败");
        Connection conn = null;
        try {
            conn = DBUtils.getDBConn_Ope();
            StringBuffer sb = new StringBuffer();
            sb.append(" UPDATE `is_equipment_info` a SET a.sfsc = 1,a.xgsj = NOW() " + "WHERE a.sb_id = ? AND a.sfsc = 0 ");
            this.update(conn, sb.toString(), new Object[]{req.getReqData().get("sbId")});
            conn.commit();
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
        return res;
    }

    public PageData unbindEquipmentById(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("设备删除失败");
        Connection conn = null;
        try {
            conn = DBUtils.getDBConn_Ope();
            StringBuffer sb = new StringBuffer();
            sb.append(" UPDATE `is_equipment_info` a SET a.wx_id = NULL " + "WHERE a.sb_id = ? AND a.sfsc = 0 ");
            this.update(conn, sb.toString(), new Object[]{req.getReqData().get("sbId")});
            conn.commit();
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
        return res;
    }

    public PageData getWorkOrder(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("查询设备信息失败");
        Connection conn = null;
        try {
            conn = DBUtils.getDBConn_Ope();
            StringBuffer sb = new StringBuffer();
            sb.append(" select dd_id from is_equipment_info where sb_id= '")
                    .append(req.getReqData().get("sbId"))
                    .append("' ");
            List<Map<String, Object>> list0 = this.queryForList(conn, sb.toString(), new Object[]{});
            if (list0.size() > 0) {
                String ddid = (String) list0.get(0).get("dd_id");
                sb = new StringBuffer();
                StringBuffer sbfrom = new StringBuffer();
                sb.append(" SELECT user_id,cjsj,`desc` FROM is_work_order_info where dd_id=? ");
                sb.append(" order by cjsj desc ");
                sb = DBUtils.sql_check(sb);
                sbfrom.append(sb.toString());
                int idx = sbfrom.indexOf(" FROM ");
                sbfrom.delete(0, idx);
                Page page = new Page(conn, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, new Object[]{ddid});
                sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
                List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{ddid});
                res.setPage(list);
            }
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
        return res;
    }

    @SuppressWarnings("unchecked")
    public PageData getCardInfo(DataReq req) {
        PageData res = new PageData();
        Connection conn = null;
        try {
            conn = DBUtils.getDBConn_Ope();
            StringBuffer sb = new StringBuffer();
            sb.append(" select wx_id from is_equipment_info where sb_id= '")
                    .append(req.getReqData().get("sbId"))
                    .append("' ");
            List<Map<String, Object>> list0 = this.queryForList(conn, sb.toString(), new Object[]{});
            if (list0.size() > 0) {
                String wxId = (String) list0.get(0).get("wx_id");
                sb = new StringBuffer();
                sb.append(" select wx_kh from is_wxsb where wx_id = '").append(wxId).append("' ");
                List<Map<String, Object>> list1 = this.queryForList(conn, sb.toString(), new Object[]{});
                if (list1.size() > 0) {
                    String wx_kh = (String) list1.get(0).get("wx_kh");
                    JSONObject json = ShakehandsUtil.getCardInfo(wx_kh);
                    if (json != null) {
                        Map<String, Object> map = JSONObject.parseObject(json.toJSONString(), Map.class);
                        Map<String, Object> map0 = (Map<String, Object>) map.get("data");
                        String expire_time = map0.get("expire_time").toString();
                        BigDecimal gprs_all = new BigDecimal(map0.get("gprs_all").toString());
                        BigDecimal gprs_use = new BigDecimal(map0.get("gprs_use").toString());
                        list0.get(0).put("expire_time", expire_time);
                        list0.get(0).put("syll", gprs_all.subtract(gprs_use).doubleValue());
                    }
                }
            }
            res.setPage(list0);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
        return res;
    }

    /**
     * 煤改电采集数据同步到厂商的统一协议id
     */
    private String ctceProtocolId = "dp_16739231727449370759217621118319280";

    public PageData getEquipmentInfoOperationCondition(DataReq req) {
        PageData res = new PageData();
        HashMap<String, Object> param = req.getReqData();
        Connection conn = null;
        conn = DBUtils.getDBConn_Ope();
        StringBuffer sb = new StringBuffer();
        try {
            final String userType = req.getUinfo().getUserType();

            //根据设备id获取相关协议
            Map<String, Object> map = null;
            String protocolId = null;
            if (StrUtil.isNotEmpty((String) param.get("sbId"))) {
                //根据设备类型获取对应的协议id
                sb.delete(0, sb.length());
                sb.append("SELECT protocol_id, is_master FROM is_equipment_info WHERE sb_id = ?");
                map = this.queryForList(conn, sb.toString(), new Object[]{param.get("sbId")}).get(0);
                if (CollUtil.isNotEmpty(map) && map.containsKey("protocol_id")) {
                    protocolId = (String) map.get("protocol_id");
                }
                //设备上没有协议id,根据绑定dtu查询协议
                else {
                    //查询设备所绑定dtu使用的协议
                    sb.delete(0, sb.length());
                    sb.append("SELECT iw.protocol_id FROM is_equipment_info iei, is_wxsb iw WHERE iei.wx_id = iw.wx_id AND iei.sb_id = ? ");
                    List<Map<String, Object>> protocolIds = this.queryForList(conn, sb.toString(), new Object[]{param.get("sbId")});
                    protocolId = (String) protocolIds.get(0).get("protocol_id");
                    //dtu绑定的协议不为空,更新设备上的协议
                    if (StrUtil.isNotEmpty(protocolId)) {
                        sb.delete(0, sb.length());
                        sb.append("UPDATE is_equipment_info SET protocol_id = ? WHERE sb_id = ?");
                        int i = this.update(conn, sb.toString(), new Object[]{protocolId, param.get("sbId")});
                    }
                }
            }
            int isPlc = 0;
            if (CollUtil.isNotEmpty(map)) {
                final Object isMaster = map.get("is_master");
                if (isMaster != null) {
                    isPlc = 1;
                }
            }

            List<Map<String, Object>> fieldDtoList = null;
            //设备协议任然为空,则设为煤改电协议
            if (StrUtil.isEmpty(protocolId)) {
                protocolId = ctceProtocolId;
            }

            // 获取用户设备数据项访问权限
            final List<ProtocolDataItemAuth> userAuths = protocolDataItemAuthMapper.selectList(new QueryWrapper<ProtocolDataItemAuth>().eq(ProtocolDataItemAuth.PROTOCOL_ID, protocolId)
                    .eq(ProtocolDataItemAuth.USER_TYPE, userType)
                    .orderByDesc(ProtocolDataItemAuth.FIELD_NAME));
            Set<String> userAuthSet = new HashSet<>();
            for (ProtocolDataItemAuth auth : userAuths) {
                userAuthSet.add(auth.getFieldName());
            }

            List<Map<String, Object>> classify = null;
            if (StrUtil.isNotEmpty((String) param.get("sbId"))) {
                //煤改电的同步数据获取所有字段
                if (protocolId.equals(ctceProtocolId)) {
                    //	                fieldDtoList = warnRuleInfoMapper.getAllFields(ctceProtocolId);
                    sb.delete(0, sb.length());
                    sb.append("SELECT d.field_name AS dataItem,\r\n" + "               c.`name`     AS dataName,\r\n" + "               c.classify   AS classifyId,\r\n" + "               c.unit_value AS unitValueId,\r\n" + "               c.data_item_id AS dataItemId,\r\n" + "               c.is_check_fault AS checkFault,\r\n" + "               (\r\n" + "                   SELECT g.title\r\n" + "                   FROM is_dict_type_info i\r\n" + "                            LEFT JOIN is_dict_value_info g ON i.type_id = g.type_id\r\n" + "                   WHERE i.type_name = 'metering_state'\r\n" + "                     AND g.dic_no = c.unit_value\r\n" + "               )            AS unitValueName,\r\n" + "               (\r\n" + "                   SELECT n.title\r\n" + "                   FROM is_dict_type_info m\r\n" + "                            LEFT JOIN is_dict_value_info n ON m.type_id = n.type_id\r\n" + "                   WHERE m.type_name = 'protocol_parameter_classification'\r\n" + "                     AND n.dic_no = c.classify AND n.manufacture_id = c.manufacture_id\r\n" + "               )            AS classifyName\r\n" + "        FROM is_device_protocol_data_item_info c\r\n" + "                 INNER JOIN is_data_item_template_info d ON c.template_id = d.template_id\r\n" + "            AND d.is_deleted = 0 AND c.is_deleted = 0 AND (is_read_write != 3 or is_read_write is null) \r\n" + "        WHERE c.protocol_id = ? ");
                    fieldDtoList = this.queryForList(conn, sb.toString(), new Object[]{ctceProtocolId});
                }
                //根据设备id获取该设备数据项
                else {
                    //	                fieldDtoList = warnRuleInfoMapper.
                    //	                        getFieldDtoBySbId(vo.getSbId(), vo.getType(), isPlc, vo.getUnit(), vo.getName(), vo.getFields(),vo.getRylb());
                    sb.delete(0, sb.length());
                    sb.append("SELECT\r\n" + "        d.field_name AS dataItem,\r\n" + "        ifnull(n.fieldtitle,IF(c.fore_name != '', c.fore_name, c.name)) dataName,\r\n" + "        IF(c.fore_name != '', c.fore_name, c.name) name,\r\n" + "        if(classifyDict.title is null, null, c.classify) AS classifyId,\r\n" + "        c.unit_value AS unitValueId,\r\n" + "        unitDict.title AS unitValueName,\r\n" + "        classifyDict.title AS classifyName,\r\n" + "        c.data_item_id AS dataItemId,\r\n" + "        c.is_check_fault AS checkFault\r\n" + "        FROM\r\n" + "        is_device_protocol_data_item_info c\r\n" + "        INNER JOIN is_data_item_template_info d ON c.template_id=d.template_id and d.field_name not like 'temp%'\r\n" + "        AND d.is_deleted = 0 AND c.is_deleted = 0 AND (is_read_write != 3 or is_read_write is null) \r\n" + "        INNER JOIN is_equipment_info a ON a.protocol_id = c.protocol_id\r\n" + "        LEFT JOIN is_warn_rule_info e ON c.data_item_id = e.data_item_id AND e.sblx_id = a.sblx_id\r\n" + "        AND e.sfsc = 0\r\n" + "        LEFT JOIN is_dict_value_info unitDict ON unitDict.type_id = 'd_type_28' AND unitDict.dic_no = c.unit_value AND unitDict.sfsc = 0\r\n" + "        AND unitDict.sfty = 0\r\n" + "        LEFT JOIN is_dict_value_info classifyDict ON 'd_type_33' = classifyDict.type_id AND classifyDict.dic_no = c.classify\r\n" + "        AND classifyDict.manufacture_id = c.manufacture_id AND classifyDict.sfty = 0 AND classifyDict.sfsc = 0\r\n" + "        left join nes_custom_field n on a.protocol_id=n.protocol_id and d.field_name=n.fieldname\r\n"
                            //                            + "        left join nes_custom_disp_field nd on a.protocol_id=nd.protocol_id and d.field_name=nd.fieldname and nd.type = 0\r\n"
                            + "        WHERE\r\n" + "        a.sb_id = ?");
                    if (!StringUtils.isEmpty((String) param.get("name"))) {
                        sb.append(" and (c.name like concat('%', " + param.get("name") + ", '%') or c.fore_name like concat('%', " + param.get("name") + ", '%')) ");
                    }
                    if ("1".equals(isPlc)) {
                        sb.append(" AND EXISTS (SELECT 1 FROM is_device_item idi WHERE idi.item_id = c.data_item_id AND idi.sb_id = a.sb_id) ");
                    }
                    if ("1".equals(param.get("unit"))) {
                        sb.append("  AND unitDict.id IS NOT NULL ");
                    }
                    sb.append(" AND (e.id IS NULL OR (e.sfsc = 0");
                    if ("0".equals(param.get("type"))) {
                        sb.append("  AND e.sjdcsxs = 1 ");
                    } else if ("1".equals(param.get("type"))) {
                        sb.append(" AND e.sjsfyx = 1 ");
                    }
                    sb.append(")) ");
                    sb.append(" order by classifyDict.is_default,n.sort,dataName ");
                    fieldDtoList = this.queryForList(conn, sb.toString(), new Object[]{param.get("sbId")});

                    // 权限控制
                    if (CollUtil.isNotEmpty(userAuthSet)) {
                        Iterator<Map<String, Object>> iterator = fieldDtoList.iterator();
                        while (iterator.hasNext()) {
                            Map<String, Object> fieldDto = iterator.next();
                            if (!userAuthSet.contains((String) fieldDto.get("dataItem"))) {
                                iterator.remove();
                            }
                        }
                    }
                }
                //根据设备id获取已使用的参数分类
                //	            classify = warnRuleInfoMapper.getClassifyBySbId(vo.getSbId(), vo.getType());
                sb.delete(0, sb.length());
                sb.append("SELECT\r\n" + "        c.classify AS classifyId,\r\n" + "        idvi.title AS classifyName\r\n" + "        FROM\r\n" + "        is_device_protocol_data_item_info c\r\n" + "        LEFT JOIN is_data_item_template_info d ON c.template_id=d.template_id AND d.is_deleted=0\r\n" + "        LEFT JOIN is_equipment_info a ON a.protocol_id = c.protocol_id AND a.sfsc = 0\r\n" + "        LEFT JOIN is_warn_rule_info e ON c.data_item_id = e.data_item_id AND e.sblx_id = a.sblx_id\r\n" + "        LEFT JOIN is_dict_value_info idvi ON idvi.dic_no = c.classify AND idvi.type_id = 'd_type_33'\r\n" + "        AND idvi.manufacture_id = c.manufacture_id\r\n" + "        WHERE\r\n" + "        a.sb_id = ?\r\n" + "        AND c.is_deleted = 0\r\n" + "        AND idvi.title != ''\r\n" + "        AND idvi.sfty = 0\r\n" + "        AND idvi.sfsc = 0\r\n" + "        AND\r\n" + "        (EXISTS (SELECT 1 FROM is_device_item idi WHERE idi.sb_id = a.sb_id AND idi.is_deleted = 0 AND c.data_item_id =\r\n" + "        idi.item_id)\r\n" + "        OR a.is_master IS NULL)\r\n" + "        AND (e.id IS NULL OR\r\n" + "        (e.sfsc = 0");
                if ("0".equals(param.get("type"))) {
                    sb.append(" AND e.sjdcsxs = 1 ");
                } else if ("1".equals(param.get("type"))) {
                    sb.append(" AND e.sjsfyx = 1 ");
                }
                sb.append(")) GROUP BY idvi.is_default,c.classify ");
                classify = this.queryForList(conn, sb.toString(), new Object[]{param.get("sbId")});
            }
            classify.removeAll(Collections.singleton(null));
            //查询对比classifyId，如果fieldDtoList没有classifyId则去掉classify中的
            if (classify != null && !classify.isEmpty() && fieldDtoList != null && !fieldDtoList.isEmpty()) {
                Iterator<Map<String, Object>> it = classify.iterator();
                while (it.hasNext()) {
                    Map<String, Object> dto = it.next();
                    int cnt = 0;
                    for (int i = 0; i < fieldDtoList.size(); i++) {
                        String classifyId = (String) fieldDtoList.get(i).get("classifyId");
                        if (null != classifyId && classifyId.equals(dto.get("classifyId"))) {
                            cnt++;
                        }
                    }
                    if (cnt == 0) {
                        it.remove();
                    }
                }
            }
            Set<String> currentWarn = new HashSet<>();
            Set<String> warnedDataItems = new HashSet<>();
            String fault = "";
            if (fieldDtoList.size() > 0) {
                //获取该设备最新运行情况数据
                //	            List<HashMap<String, Object>> rawDataList = manufactureDataMapper.
                //	                    getEquipmentInfoOperationCondition(vo.getSbId(), vo.getDtuId());
                sb.delete(0, sb.length());
                sb.append("SELECT other_data, gmt_create, warn_data_items, fault_codes\r\n" + " FROM is_norm_data a " + " LEFT JOIN is_equipment_info b ON a.sb_id = b.sb_id AND b.sfsc = 0 AND a.dtu_id = b.wx_id " + " WHERE 1=1 and b.sb_id = ? " + " limit 1 ");
                List<Map<String, Object>> rawDataList = this.queryForList(conn, sb.toString(), new Object[]{param.get("sbId")});
                List<HashMap<String, Object>> data = new ArrayList<>();
                //获取other data
                if (CollUtil.isNotEmpty(rawDataList)) {
                    data.add(handleOtherData(rawDataList.get(0)));
                    //获取预警的数据项
                    Object warnDataItems = rawDataList.get(0).get("warn_data_items");
                    if (ObjectUtil.isNotNull(warnDataItems)) {
                        String itemJson = warnDataItems.toString();
                        if (StrUtil.isNotEmpty(itemJson)) {
                            Map<String, String> allWarns = JSONObject.parseObject(itemJson, new TypeReference<Map<String, String>>() {
                            });
                            if (CollUtil.isNotEmpty(allWarns)) {
                                for (Map.Entry<String, String> entry : allWarns.entrySet()) {
                                    if (StrUtil.isBlank(entry.getValue())) {
                                        currentWarn.add(entry.getKey());
                                    }
                                }
                                //	                            warnedDataItems = equipmentWarnInfoMapper.getWarnedDataItems(currentWarn, isPlc);
                                sb.delete(0, sb.length());
                                if (isPlc == 1) {
                                    sb.append("select distinct c.data_item_id\r\n" + "            from is_warningrules a\r\n" + "            inner join is_data_item_template_info b on a.fldList = concat(b.field_name, ',')\r\n" + "            inner join is_device_protocol_data_item_info c on c.template_id = b.template_id and c.is_deleted = 0\r\n" + "            inner join is_device_item d on d.item_id = c.data_item_id and d.is_deleted = 0 and d.sb_id = a.sb_id\r\n" + "            left join is_ignored_item ig on ig.type = 2 and ig.ignored_item = a.uuid\r\n" + "            where\r\n" + "            a.isdelete = 0 and ig.id is null ");
                                    Iterator<String> iterator = currentWarn.iterator();
                                    int i = 0;
                                    while (iterator.hasNext()) {
                                        String next = iterator.next();
                                        if (i == 0) {
                                            sb.append(" and a.uuid in ( ");
                                            sb.append("'").append(next).append("'");
                                        } else {
                                            sb.append(",'").append(next).append("'");
                                        }
                                        i++;
                                    }
                                    if (i > 0) {
                                        sb.append(") ");
                                    }
                                } else if (isPlc == 0) {
                                    sb.append("select distinct c.data_item_id\r\n" + "            from is_warningrules a\r\n" + "            inner join is_data_item_template_info b on a.fldList = concat(b.field_name, ',')\r\n" + "            inner join is_device_protocol_data_item_info c on c.template_id = b.template_id and c.is_deleted = 0\r\n" + "            inner join is_equipment_info d on d.sb_id = a.sb_id and c.protocol_id = d.protocol_id\r\n" + "            left join is_ignored_item ig on ig.type = 2 and ig.ignored_item = a.uuid\r\n" + "            where\r\n" + "            a.isdelete = 0 and ig.id is null ");
                                    Iterator<String> iterator = currentWarn.iterator();
                                    int i = 0;
                                    while (iterator.hasNext()) {
                                        String next = iterator.next();
                                        if (i == 0) {
                                            sb.append(" and a.uuid in ( ");
                                            sb.append("'").append(next).append("'");
                                        } else {
                                            sb.append(",'").append(next).append("'");
                                        }
                                        i++;
                                    }
                                    if (i > 0) {
                                        sb.append(") ");
                                    }

                                }
                                warnedDataItems = new HashSet(this.queryForList(conn, sb.toString(), new Object[]{}));
                            }
                        }
                    }
                    //获取故障情况
                    Object faultCodes = rawDataList.get(0).get("fault_codes");
                    if (ObjectUtil.isNotNull(faultCodes)) {
                        fault = faultCodes.toString();
                    }
                }
                Set<String> faultSet = new HashSet<>();
                //查询有哪些数据项正在故障状态
                if (StrUtil.isNotEmpty(fault) && StrUtil.isNotEmpty((String) param.get("sbId"))) {
                    //切割故障情况
                    String[] faults = fault.split(",");
                    //	                List<String> faultItemIds = equipmentFaultInfoMapper.getFaultItemIds(faults, vo.getSbId());
                    sb.delete(0, sb.length());
                    sb.append("SELECT idpdii.data_item_id\r\n" + "        FROM is_device_protocol_data_item_info idpdii,\r\n" + "             is_equipment_info iei\r\n" + "                WHERE idpdii.protocol_id = iei.protocol_id\r\n" + "                  AND iei.sb_id = ? \r\n" + "                  AND idpdii.is_deleted = 0\r\n" + "                  AND idpdii.is_check_fault = 1\r\n" + "                  AND fault_code IN (");
                    for (int i = 0; i < faults.length; i++) {
                        if (i == 0) {
                            sb.append("'").append(faults[i]).append("'");
                        } else {
                            sb.append(",'").append(faults[i]).append("'");
                        }
                    }
                    sb.append(" ) UNION ALL ");
                    sb.append(" SELECT idpdii.data_item_id\r\n" + "        FROM is_device_protocol_data_item_info idpdii,\r\n" + "             is_equipment_info iei,\r\n" + "             is_data_item_field idf\r\n" + "                WHERE idpdii.protocol_id = iei.protocol_id\r\n" + "                  AND iei.sb_id = ?\r\n" + "                  AND idpdii.is_deleted = 0\r\n" + "                  AND idpdii.is_check_fault = 1\r\n" + "                  AND idpdii.data_item_id = idf.data_item_id\r\n" + "                  AND idf.is_deleted = 0\r\n" + "                  AND idf.name IN (");
                    for (int i = 0; i < faults.length; i++) {
                        if (i == 0) {
                            sb.append("'").append(faults[i]).append("'");
                        } else {
                            sb.append(",'").append(faults[i]).append("'");
                        }
                    }
                    sb.append(" ) ");
                    List<String> faultItemIds = new ArrayList();
                    List<Map<String, Object>> faultItemObjects = this.queryForList(conn, sb.toString(), new Object[]{param.get("sbId"), param.get("sbId")});
                    for (Map<String, Object> obj : faultItemObjects) {
                        String item = String.valueOf(obj.get("data_item_id"));
                        faultItemIds.add(item);
                    }
                    faultSet = new HashSet<>(faultItemIds);
                }
                if (data.size() == 1) {
                    //	                Map<String, FunctionDataDto> commands = getFormattedEquipCommands(rawDataList, protocolId, vo.getSbId(), isPlc == 1, vo.getDtuId(),vo.getRylb());
                    for (Map<String, Object> fieldDto : fieldDtoList) {
                        //存放指令
                        String filedName = (String) fieldDto.get("dataItem");
                        String dataItemId = (String) fieldDto.get("dataItemId");
                        //	                    if (CollUtil.isNotEmpty(commands) && commands.containsKey(filedName)) {
                        //	                        FunctionDataDto command = commands.get(filedName);
                        //	                        fieldDto.setCommand(command);
                        //	                    }
                        //如果当前采集数据该数据项报警, 添加一个报警标识
                        fieldDto.put("status", "正常");
                        if (CollUtil.isNotEmpty(warnedDataItems) && warnedDataItems.contains(dataItemId)) {
                            fieldDto.put("status", "预警");
                        }
                        if (faultSet.contains(dataItemId)) {
                            fieldDto.put("status", "故障");
                        }
                        if (data.get(0).containsKey(filedName)) {
                            continue;
                        }
                        data.get(0).put(filedName, "");
                    }
                }
                //封装数据
                Map<String, Object> dto = new HashMap<>();
                if (CollUtil.isNotEmpty(data)) {
                    if (data.get(0).containsKey("gmt_create")) {
                        dto.put("cjsj", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format((Date) data.get(0)
                                .get("gmt_create")));
                    }
                }

                if (data.size() == 1) {
                    // 查询故障项的关联数据
                    sb.setLength(0);
                    sb.append("select a.fault_data_item_id as faultDataItemId,")
                            .append(" bt.field_name as faultFieldName,")
                            .append(" ct.field_name as relatedFieldName,")
                            .append(" c.name as relatedDataItemName")
                            .append(" from fault_data_item_relation a")
                            .append(" inner join is_device_protocol_data_item_info b on a.fault_data_item_id = b.data_item_id")
                            .append(" inner join is_data_item_template_info bt on b.template_id = bt.template_id")
                            .append(" inner join is_device_protocol_data_item_info c on a.related_data_item_id = c.data_item_id")
                            .append(" inner join is_data_item_template_info ct on c.template_id = ct.template_id")
                            .append(" where a.protocol_id = ? and b.is_deleted = 0 and c.is_deleted = 0");
                    List<Map<String, Object>> faultRelatedDataList = this.queryForList(conn, sb.toString(), new Object[]{protocolId});


                    for (Map<String, Object> field : fieldDtoList) {
                        // tinyint类型的数据转换为boolean类型
                        Object checkFaultInt = field.getOrDefault("checkFault", 0);
                        assert checkFaultInt instanceof Integer;
                        final boolean checkFault = ((Integer) checkFaultInt) == 1;
                        if (checkFault) {
                            List<Map<String, Object>> faultRelatedData = new ArrayList<>();
                            for (Map<String, Object> faultRelatedDataItem : faultRelatedDataList) {
                                if (field.get("dataItemId").equals(faultRelatedDataItem.get("faultDataItemId"))) {
                                    String relatedFieldName = (String) faultRelatedDataItem.get("relatedFieldName");
                                    faultRelatedData.add(MapBuilder.create(new HashMap<String, Object>())
                                            .put("faultFieldName", faultRelatedDataItem.get("faultFieldName"))
                                            .put("relatedFieldName", faultRelatedDataItem.get("relatedFieldName"))
                                            .put("relatedDataValue", data.get(0).get(relatedFieldName))
                                            .put("relatedDataItemName", faultRelatedDataItem.get("relatedDataItemName"))
                                            .build());
                                }
                            }
                            if (CollUtil.isNotEmpty(faultRelatedData)) {
                                field.put("faultRelatedData", faultRelatedData);
                            }
                        }
                    }
                }

                dto.put("fields", fieldDtoList);
                dto.put("data", data);

                List<Map<String, Object>> classifyList = new ArrayList<>();
                // 特殊处理数据项分类
                for (Map<String, Object> map1 : classify) {
                    if (ObjectUtil.isNotNull(map1.get("classifyName")) && "运行参数".equals(map1.get("classifyName"))) {
                        classifyList.add(0, map1);
                    } else {
                        classifyList.add(map1);
                    }
                }

                dto.put("classify", classifyList);
                res.setExtData(dto);
            }

            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
        return res;
    }

    /**
     * 处理other data
     */
    public HashMap<String, Object> handleOtherData(Map<String, Object> rawData) {
        //获取other data
        String unzipJson = (String) rawData.get("other_data");
        HashMap<String, Object> res = new HashMap<>(1);
        //解压other data
        String otherDataJson = ZipUtils.gunzip(unzipJson);
        //原始数据如果为压缩,解压结果为空,则取原始数据
        if (StrUtil.isEmpty(otherDataJson)) {
            otherDataJson = unzipJson;
        }
        if (StrUtil.isNotEmpty(otherDataJson)) {
            List<OtherData> otherDataList = JSONArray.parseArray(otherDataJson, OtherData.class);
            for (OtherData otherData : otherDataList) {
                if (ObjectUtil.isNotNull(otherData.getF()) && ObjectUtil.isNotNull(otherData.getV())) {
                    res.put(otherData.getF(), otherData.getV());
                    res.put(otherData.getF() + "_s", otherData.getS());
                }
            }
        }
        res.put("gmt_create", rawData.get("gmt_create"));
        return res;
    }

    /**
     * 处理other data
     */
    HashMap<String, Object> handleOtherDataRaw(Map<String, Object> rawData) {
        //获取other data
        String unzipJson = (String) rawData.get("other_data");
        //解压other data
        String otherDataJson = ZipUtils.gunzip(unzipJson);
        //原始数据如果为压缩,解压结果为空,则取原始数据
        if (StrUtil.isEmpty(otherDataJson)) {
            otherDataJson = unzipJson;
        }
        HashMap<String, Object> temp = new HashMap<>(1);
        if (StrUtil.isNotEmpty(otherDataJson)) {
            List<OtherData> otherDataList = JSONArray.parseArray(otherDataJson, OtherData.class);
            for (OtherData otherData : otherDataList) {
                temp.put(otherData.getF(), otherData.getS());
            }
        }
        temp.put("gmt_create", rawData.get("gmt_create"));
        return temp;
    }

    public PageData addCustomField(DataReq req) {
        HashMap<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("保存失败！");
        Connection conn = null;
        try {
            conn = DBUtils.getDBConn_Ope();
            StringBuffer sb = new StringBuffer();
            //删除此设备id下所有的字段配置项
            sb.append("delete from nes_custom_field WHERE protocol_Id = '")
                    .append(param.get("protocolId"))
                    .append("' ");
            int result = this.update(conn, sb.toString(), new Object[]{});
            if (result <= 0) {
                conn.rollback();
                return res;
            }
            JSONArray fieldnames = (JSONArray) param.get("fieldnames");
            JSONArray fieldtitles = (JSONArray) param.get("fieldtitles");
            List<Map<String, Object>> list = new ArrayList<>();
            for (int i = 0; i < fieldnames.size(); i++) {
                Map<String, Object> vo = new HashMap();
                vo.put("fieldname", fieldnames.get(i));
                vo.put("fieldtitle", fieldtitles.get(i));
                vo.put("sort", i);
                vo.put("indexSort", i);
                vo.put("protocolId", param.get("protocolId"));
                vo.put("isShow", 1);
                vo.put("lastUpdateUserID", 1);
                vo.put("sbId", param.get("sbId"));
                list.add(vo);
            }
            //批量插入自定义字段
            sb.delete(0, sb.length());
            sb.append("INSERT INTO `nes_ope_prod`.`nes_custom_field` (\r\n" + "          `protocol_id`,\r\n");
            if (!StringUtils.isEmpty((String) param.get("sbId"))) {
                sb.append("sb_id,");
            }
            sb.append("          `fieldname`,\r\n" + "          `fieldtitle`,\r\n" + "          `sort`,\r\n" + "          `createUserID`,\r\n" + "          `createTime`,\r\n" + "          `lastUpdateUserID`,\r\n" + "          `lastUpdateTime`,\r\n" + "          `isShow`,\r\n" + "          `displayOnIndex`,\r\n" + "          `indexSort`\r\n" + "        ) VALUES ");
            for (int i = 0; i < list.size(); i++) {
                if (i == 0) {
                    sb.append("(");

                } else {
                    sb.append(",(");
                }
                sb.append(list.get(i).get("protocolId"));
                if (!StringUtils.isEmpty((String) list.get(i).get("sbId"))) {
                    sb.append(",").append(list.get(i).get("sbId"));
                }
                sb.append(",").append(list.get(i).get("fieldname"));
                sb.append(",").append(list.get(i).get("fieldtitle"));
                sb.append(",").append(list.get(i).get("sort"));
                sb.append(",").append(list.get(i).get("createUserID"));
                sb.append(",").append("now()");
                sb.append(",").append(list.get(i).get("lastUpdateUserID"));
                sb.append(",").append("now()");
                sb.append(",").append(list.get(i).get("isShow"));
                sb.append(",").append(list.get(i).get("displayOnIndex"));
                sb.append(",").append(list.get(i).get("indexSort"));
                sb.append(")");
            }
            result = this.update(conn, sb.toString(), new Object[]{});
            if (result <= 0) {
                conn.rollback();
                return res;
            }

            conn.commit();
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
        return res;
    }

    public PageData saveDataItemGroups(DataReq req) {
        HashMap<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("保存失败！");
        Connection conn = null;
        try {
            conn = DBUtils.getDBConn_Ope();
            StringBuffer sb = new StringBuffer();
            if (!(param.get("dataItemIds") instanceof String[])) {
                if (StrUtil.isNotBlank((String) param.get("dataItemId"))) {
                    sb.delete(0, sb.length());
                    sb.append("update is_device_protocol_data_item_info\r\n" + "        set classify = '" + param.get("classify") + "' " + "        where data_item_id = '")
                            .append(param.get("dataItemId"))
                            .append("' ");
                }
            } else {
                String classify = (String) param.get("classify");
                JSONArray dataItemIds = (JSONArray) param.get("dataItemIds");
                List<Map<String, Object>> list = new ArrayList<>();
                for (int i = 0; i < dataItemIds.size(); i++) {
                    Map<String, Object> saveProtocolVo = new HashMap<>();
                    saveProtocolVo.put("dataItemId", dataItemIds.get(i));//setDataItemId(dataItemIds[i]);
                    list.add(saveProtocolVo);
                }
                if (list.size() > 0) {
                    //	                deviceProtocolDataItemInfoMapper.saveDataItemGroupsBatch(list,classify);
                    sb.delete(0, sb.length());
                    sb.append("update is_device_protocol_data_item_info" + " set classify = '" + param.get("classify") + "' " + " where data_item_id in (");
                    for (int i = 0; i < list.size(); i++) {
                        if (i == 0) {
                            sb.append("'").append(list.get(i).get("dataItemId")).append("' ");
                        } else {
                            sb.append(",'").append(list.get(i).get("dataItemId")).append("' ");
                        }
                    }
                    sb.append(") ");
                }
            }
            int i = this.update(conn, sb.toString(), new Object[]{});
            if (i <= 0) {
                conn.rollback();
                return res;
            }
            conn.commit();
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
        return res;
    }

    public PageData getEquipmentInfoOperationConditionHistory(Connection conn, Connection conn_click,
            DataReq req) throws SQLException {
        StringBuffer sb = new StringBuffer();
        StringBuffer sbfrom = new StringBuffer();

        String sbId = (String) req.getReqData().get("sbId");
        String startTime = (String) req.getReqData().get("startTime");
        String endTime = (String) req.getReqData().get("endTime");
        String dataType = (String) req.getReqData().get("dataType");

        PageData res = new PageData();
        if (StringUtils.isEmpty(startTime)) {
            String[] date = getLastHisDateBySbID(conn_click, sbId, "2");
            startTime = date[0];
            endTime = date[1];
        }
        //获取字段分类
        sb.delete(0, sb.length());
        sb.append("SELECT d.field_name AS dataItem,\r\n" + "               c.`name`     AS dataName,\r\n" + "               c.classify   AS classifyId,\r\n" + "               c.unit_value AS unitValueId,\r\n" + "               (\r\n" + "                   SELECT g.title\r\n" + "                   FROM is_dict_type_info i\r\n" + "                            LEFT JOIN is_dict_value_info g ON i.type_id = g.type_id\r\n" + "                   WHERE i.type_name = 'metering_state'\r\n" + "                     AND g.dic_no = c.unit_value\r\n" + "               )            AS unitValueName,\r\n" + "               (\r\n" + "                   SELECT n.title\r\n" + "                   FROM is_dict_type_info m\r\n" + "                            LEFT JOIN is_dict_value_info n ON m.type_id = n.type_id\r\n" + "                   WHERE m.type_name = 'protocol_parameter_classification'\r\n" + "                     AND n.dic_no = c.classify AND n.manufacture_id = c.manufacture_id\r\n" + "               )            AS classifyName\r\n" + "        FROM is_device_protocol_data_item_info c\r\n" + "                 INNER JOIN is_data_item_template_info d ON c.template_id = d.template_id\r\n" + "            AND d.is_deleted = 0 AND c.is_deleted = 0  AND (is_read_write != 3 or is_read_write is null) \r\n" + "                 INNER JOIN is_wxsb wx ON wx.protocol_id = c.protocol_id  \r\n" + "                 INNER JOIN is_equipment_info eq ON eq.wx_id = wx.wx_id  \r\n" + "                 left JOIN nes_ope_prod.is_device_item x on x.item_id = c.data_item_id  \r\n" + "        WHERE eq.sb_id = ? and (x.sb_id=? or x.sb_id is null) ");
        List<Map<String, Object>> fieldDtoList = this.queryForList(conn, sb.toString(), new Object[]{sbId, sbId});

        List<Map<String, Object>> classifyDtoList = new ArrayList<Map<String, Object>>();

        for (int i = 0; i < fieldDtoList.size(); i++) {
            Map<String, Object> e = new LinkedHashMap<String, Object>();
            e.put("classifyId", fieldDtoList.get(i).get("classifyId"));
            e.put("classifyName", fieldDtoList.get(i).get("classifyName"));
            if (!classifyDtoList.contains(e)) {
                classifyDtoList.add(e);
            }
        }

        //获取历史数据
        sb.delete(0, sb.length());
        sb.append("SELECT formatDateTime(gmt_create,'%F %T') gmtCreate,other_data from is_norm_data_his_buffer where sb_id=? and json_data is not null ");
        sb.append(" and gmt_create>=toDateTime('")
                .append(startTime)
                .append("') and gmt_create<=toDateTime('")
                .append(endTime)
                .append("')  ");
        sb = DBUtils.sql_check(sb);
        sbfrom.append(sb.toString());
        int idx = sbfrom.indexOf(" from ");
        sbfrom.delete(0, idx);
        Page page = new Page(conn_click, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, new Object[]{sbId});

        String pxType = (String) req.getReqData().get("pxType");
        if (StringUtils.isEmpty(pxType)) {
            pxType = "ASC";
        }
        sb.append(" order by gmt_create ").append(pxType);

        if (!"2".equals(dataType)) {
            sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
        }

        List<Map<String, Object>> dataList = this.queryForList(conn_click, sb.toString(), new Object[]{sbId});
        for (int i = 0; i < dataList.size(); i++) {
            Map<String, Object> mapData = dataList.get(i);
            String other_data = (String) mapData.get("other_data");
            mapData.put("other_data", null);
            other_data = ZipUtils.gunzip(other_data);
            List<OtherData> otherDataList = JSONArray.parseArray(other_data, OtherData.class);
            for (OtherData otherData : otherDataList) {
                mapData.put(otherData.getF(), otherData.getS());
                mapData.put(otherData.getF() + "_V", otherData.getV());

            }
        }


        res.setDatas(classifyDtoList);
        res.setPage(dataList);
        res.setExtData(fieldDtoList);
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public String[] getLastHisDateBySbID(Connection conn_click, String sbId, String offset) throws SQLException {
        StringBuffer sb = new StringBuffer();
        String[] lastTime = new String[2];
        lastTime[0] = DBUtils.getDate(-3) + " 00:00:00";
        lastTime[1] = DBUtils.getDate(0) + " 23:59:59";
        sb.delete(0, sb.length());
        sb.append("SELECT formatDateTime(subtractDays(max(gmt_create),")
                .append(offset)
                .append("),'%F %T') gmtcreate, formatDateTime( max(gmt_create) ,'%F %T') gmtcreateEnd FROM is_norm_data_his_buffer where  sb_id='")
                .append(sbId)
                .append("' ");
        sb = DBUtils.sql_check(sb);
        List<Map<String, Object>> dataList = this.queryForList(conn_click, sb.toString(), new Object[]{});
        if (dataList.size() >= 0) {
            String gmtcreateEnd = (String) dataList.get(0).get("gmtcreateEnd");
            if (!StringUtils.isEmpty(gmtcreateEnd) && !gmtcreateEnd.equals("0000-00-00 00:00:00")) {
                lastTime[0] = (String) dataList.get(0).get("gmtcreate");
            }
        }

        return lastTime;
    }

    public PageData getEquipmentInfoOperationConditionHistory_old(DataReq req) {
        HashMap<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("保存失败！");
        StringBuffer sb = new StringBuffer();
        Connection conn = null;
        try {
            conn = DBUtils.getDBConn_Ope();

            //获取历史表表名
            DecimalFormat df = new DecimalFormat("0000");
            Map<String, Object> dto = new HashMap<>();
            //根据设备id获取相关协议
            Map<String, Object> map = null;
            String protocolId = null;

            String rylb = "ryfl-99";
            param.put("rylb", rylb);
            if (StrUtil.isNotEmpty((String) param.get("sbId"))) {
                sb.delete(0, sb.length());
                sb.append("SELECT protocol_id, is_master FROM is_equipment_info WHERE sb_id = ? ");
                map = this.queryForList(conn, sb.toString(), new Object[]{param.get("sbId")}).get(0);
                if (CollUtil.isNotEmpty(map) && map.containsKey("protocol_id")) {
                    protocolId = (String) map.get("protocol_id");
                } else { //设备上没有协议id,根据绑定dtu查询协议
                    sb.delete(0, sb.length());
                    sb.append("SELECT iw.protocol_id FROM is_equipment_info iei, is_wxsb iw WHERE iei.wx_id = iw.wx_id AND iei.sb_id = ? ");
                    protocolId = (String) this.queryForList(conn, sb.toString(), new Object[]{param.get("sbId")})
                            .get(0)
                            .get("protocol_id");
                    //dtu绑定的协议不为空,更新设备上的协议
                    if (StrUtil.isNotEmpty(protocolId)) {
                        sb.delete(0, sb.length());
                        sb.append("UPDATE is_equipment_info SET protocol_id = ? WHERE sb_id = ? ");
                        this.update(conn, sb.toString(), new Object[]{protocolId, param.get("sbId")});
                    }
                }
            }

            int isPlc = 0;
            if (CollUtil.isNotEmpty(map) && map.containsKey("is_master")) {
                isPlc = 1;
            }
            List<Map<String, Object>> fieldDtoList = new ArrayList<>();
            ;
            //设备协议任然为空,则设为煤改电协议
            if (StrUtil.isEmpty(protocolId) || "made_up".equals(protocolId)) {
                protocolId = ctceProtocolId;
            }
            List<Map<String, Object>> classify = null;
            if (StrUtil.isNotEmpty((String) param.get("sbId"))) {
                //煤改电的同步数据获取所有字段
                if (protocolId.equals(ctceProtocolId)) {
                    //	                fieldDtoList = warnRuleInfoMapper.getAllFields(ctceProtocolId);
                    sb.delete(0, sb.length());
                    sb.append("SELECT d.field_name AS dataItem,\r\n" + "               c.`name`     AS dataName,\r\n" + "               c.classify   AS classifyId,\r\n" + "               c.unit_value AS unitValueId,\r\n" + "               (\r\n" + "                   SELECT g.title\r\n" + "                   FROM is_dict_type_info i\r\n" + "                            LEFT JOIN is_dict_value_info g ON i.type_id = g.type_id\r\n" + "                   WHERE i.type_name = 'metering_state'\r\n" + "                     AND g.dic_no = c.unit_value\r\n" + "               )            AS unitValueName,\r\n" + "               (\r\n" + "                   SELECT n.title\r\n" + "                   FROM is_dict_type_info m\r\n" + "                            LEFT JOIN is_dict_value_info n ON m.type_id = n.type_id\r\n" + "                   WHERE m.type_name = 'protocol_parameter_classification'\r\n" + "                     AND n.dic_no = c.classify AND n.manufacture_id = c.manufacture_id\r\n" + "               )            AS classifyName\r\n" + "        FROM is_device_protocol_data_item_info c\r\n" + "                 INNER JOIN is_data_item_template_info d ON c.template_id = d.template_id\r\n" + "            AND d.is_deleted = 0 AND c.is_deleted = 0\r\n" + "        WHERE c.protocol_id = ? ");
                    fieldDtoList = this.queryForList(conn, sb.toString(), new Object[]{ctceProtocolId});
                } else { //根据设备id获取该设备数据项
                    //	                fieldDtoList = warnRuleInfoMapper.getFieldDtoBySbId(vo.getSbId(), vo.getType(), isPlc, vo.getUnit(), vo.getName(), vo.getFields(), vo.getRylb());
                    sb.delete(0, sb.length());
                    sb.append("SELECT\r\n" + "        d.field_name AS dataItem,\r\n" + "        ifnull(n.fieldtitle,IF(c.fore_name != '', c.fore_name, c.name)) dataName,\r\n" + "        IF(c.fore_name != '', c.fore_name, c.name) name,\r\n" + "        if(classifyDict.title is null, null, c.classify) AS classifyId,\r\n" + "        c.unit_value AS unitValueId,\r\n" + "        unitDict.title AS unitValueName,\r\n" + "        classifyDict.title AS classifyName,\r\n" + "        c.data_item_id AS dataItemId\r\n" + "        FROM\r\n" + "        is_device_protocol_data_item_info c\r\n" + "        INNER JOIN is_data_item_template_info d ON c.template_id=d.template_id and d.field_name not like 'temp%'\r\n" + "        AND d.is_deleted = 0 AND c.is_deleted = 0\r\n" + "        INNER JOIN is_equipment_info a ON a.protocol_id = c.protocol_id\r\n" + "        LEFT JOIN is_warn_rule_info e ON c.data_item_id = e.data_item_id AND e.sblx_id = a.sblx_id\r\n" + "        AND e.sfsc = 0\r\n" + "        LEFT JOIN is_dict_value_info unitDict ON unitDict.type_id = 'd_type_28' AND unitDict.dic_no = c.unit_value AND unitDict.sfsc = 0\r\n" + "        AND unitDict.sfty = 0\r\n" + "        LEFT JOIN is_dict_value_info classifyDict ON 'd_type_33' = classifyDict.type_id AND classifyDict.dic_no = c.classify\r\n" + "        AND classifyDict.manufacture_id = c.manufacture_id AND classifyDict.sfty = 0 AND classifyDict.sfsc = 0\r\n" + "        left join nes_custom_field n on a.protocol_id=n.protocol_id and d.field_name=n.fieldname\r\n" + "        left join nes_custom_disp_field nd on a.protocol_id=nd.protocol_id and d.field_name=nd.fieldname and nd.type = 0\r\n" + "        WHERE\r\n" + "        a.sb_id = '")
                            .append(param.get("sbId"))
                            .append("' ");
                    if (isPlc == 1) {
                        //sb.append(" AND EXISTS (SELECT 1 FROM is_device_item idi WHERE idi.item_id = c.data_item_id AND idi.sb_id = a.sb_id) ");
                    }
                    //sb.append(" and (nd.isUser=1 or nd.isUser is null ) ");
                    sb.append(" AND (e.id IS NULL OR (\r\n" + "        e.sfsc = 0 ");
                    if ("0".equals(param.get("type"))) {
                        sb.append(" AND e.sjdcsxs = 1 ");
                    } else if ("1".equals(param.get("type"))) {
                        sb.append(" AND e.sjsfyx = 1 ");
                    }
                    sb.append(" )) ");
                    sb.append(" order by classifyDict.is_default,n.sort,dataName ");
                    fieldDtoList = this.queryForList(conn, sb.toString(), new Object[]{});

                }
                //根据设备id获取已使用的参数分类
                //	            classify = warnRuleInfoMapper.getClassifyBySbId(vo.getSbId(), vo.getType());
                sb.delete(0, sb.length());
                sb.append(" SELECT\r\n" + "        c.classify AS classifyId,\r\n" + "        idvi.title AS classifyName\r\n" + "        FROM\r\n" + "        is_device_protocol_data_item_info c\r\n" + "        LEFT JOIN is_data_item_template_info d ON c.template_id=d.template_id AND d.is_deleted=0\r\n" + "        LEFT JOIN is_equipment_info a ON a.protocol_id = c.protocol_id AND a.sfsc = 0\r\n" + "        LEFT JOIN is_warn_rule_info e ON c.data_item_id = e.data_item_id AND e.sblx_id = a.sblx_id\r\n" + "        LEFT JOIN is_dict_value_info idvi ON idvi.dic_no = c.classify AND idvi.type_id = 'd_type_33'\r\n" + "        AND idvi.manufacture_id = c.manufacture_id\r\n" + "        WHERE\r\n" + "        a.sb_id = '" + param.get("sbId") + "' " + "        AND c.is_deleted = 0\r\n" + "        AND idvi.title != ''\r\n" + "        AND idvi.sfty = 0\r\n" + "        AND idvi.sfsc = 0\r\n" + "        AND\r\n" + "        (EXISTS (SELECT 1 FROM is_device_item idi WHERE idi.sb_id = a.sb_id AND idi.is_deleted = 0 AND c.data_item_id =\r\n" + "        idi.item_id)\r\n" + "        OR a.is_master IS NULL)" + "		   AND (e.id IS NULL OR\r\n" + "        (e.sfsc = 0 ");
                if ("0".equals(param.get("type"))) {
                    sb.append(" AND e.sjdcsxs = 1 ");
                } else if ("1".equals(param.get("type"))) {
                    sb.append(" AND e.sjsfyx = 1 ");
                }
                sb.append(" )) ");
                sb.append("GROUP BY idvi.is_default,c.classify ");
                classify = this.queryForList(conn, sb.toString(), new Object[]{});
            }
            if (fieldDtoList.size() > 0) {
                //根据设备id获取已使用的参数分类
                classify.removeAll(Collections.singleton(null));
                if (ObjectUtil.isNull((String) param.get("pageid"))) {
                    param.put("pageid", 1);
                }
                if (ObjectUtil.isNull((String) param.get("pagesize"))) {
                    param.put("pagesize", 20);
                }
                String wxId = "";
                if (StrUtil.isNotEmpty((String) param.get("sbId"))) {
                    //	                wxId = equipmentInfoMapper.getWxIdBySbId(vo.getSbId());
                    sb.delete(0, sb.length());
                    sb.append("SELECT wx_id\r\n" + "        FROM is_equipment_info\r\n" + "        WHERE sb_id = ? \r\n" + "          AND sfsc = 0");
                    wxId = (String) this.queryForList(conn, sb.toString(), new Object[]{param.get("sbId")})
                            .get(0)
                            .get("wx_id");
                }
                if ("0".equals(param.get("pageid")) && "0".equals(param.get("pagesize"))) {
                    param.put("pageid", 1);
                    param.put("pagesize", 999999);
                }
                //获取该设备历史运行情况数据
                //	            List<HashMap<String, Object>> rawDataList = isNormDataHisMapper.
                //	                    getEquipmentInfoOperationConditionHistory(fieldDtoList, vo.getSbId(), wxId,
                //	                            vo.getStartDate(), vo.getEndDate());
                sb.delete(0, sb.length());
                sb.append("SELECT\r\n" + "        other_data,\r\n" + "        gmt_create\r\n" + "        from \r\n" + "        is_norm_test_data_his\r\n" + "        WHERE 1=1 ");
                if (!StringUtils.isEmpty((String) param.get("sbId"))) {
                    sb.append(" and sb_id='").append(param.get("sbId")).append("' ");
                }
                if (!StringUtils.isEmpty((String) param.get("startDate"))) {
                    sb.append(" AND UNIX_TIMESTAMP(gmt_create)*1000>='").append(param.get("startDate")).append("' ");
                }
                if (!StringUtils.isEmpty((String) param.get("endDate"))) {
                    sb.append(" AND UNIX_TIMESTAMP(gmt_create)*1000<='").append(param.get("endDate")).append("' ");
                }
                sb.append(" ORDER BY gmt_create DESC ");
                sb = DBUtils.sql_check(sb);
                StringBuffer sbfrom = new StringBuffer();
                sbfrom.append(sb.toString());
                int idx = sbfrom.indexOf(" from ");
                sbfrom.delete(0, idx);
                Page page = new Page(conn, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, new Object[]{});
                sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
                List<Map<String, Object>> rawDataList = this.queryForList(conn, sb.toString(), new Object[]{});
                List<Map<String, Object>> data = new ArrayList<Map<String, Object>>();
                //保存单位
                List<Map<String, Object>> unitList = new ArrayList<>();
                HashSet<String> unitSet = new HashSet<>();
                //处理other data
                for (Map<String, Object> rawData : rawDataList) {
                    HashMap<String, Object> otherData = handleOtherData(rawData);
                    //只保留查询得到的字段
                    HashMap<String, Object> finalOtherData = new HashMap<>(fieldDtoList.size());
                    for (Map<String, Object> fieldDto : fieldDtoList) {
                        finalOtherData.put((String) fieldDto.get("dataItem"), otherData.get(fieldDto.get("dataItem")));
                        finalOtherData.put(fieldDto.get("dataItem") + "_s", otherData.get(fieldDto.get("dataItem") + "_s"));
                        if (!unitSet.contains(fieldDto.get("unitValueId"))) {
                            unitSet.add((String) fieldDto.get("unitValueId"));
                            String unitId = (String) fieldDto.get("unitValueId");
                            String unitName = (String) fieldDto.get("unitValueName");
                            if (StrUtil.isEmpty(unitName)) {
                                unitName = "无单位";
                            }
                            String finalUnitName = unitName;
                            Map<String, Object> dataUnit = new HashMap();
                            dataUnit.put("unitId", unitId);
                            dataUnit.put("finalUnitName", finalUnitName);
                            unitList.add(dataUnit);
                        }
                    }
                    finalOtherData.put("gmt_create", otherData.get("gmt_create"));
                    data.add(finalOtherData);
                }
                rawDataList.clear();
                rawDataList.addAll(data);
                if (data.size() > 0) {
                    dto.put("data", rawDataList);
                }
                //封装数据  根据查询条件过滤
                if (StringUtils.isNotBlank((String) param.get("classifyId"))) {
                    int len = 0;
                    while (len < fieldDtoList.size()) {
                        if (!param.get("classifyId").equals(fieldDtoList.get(len).get("classifyId"))) {
                            fieldDtoList.remove(len);
                        } else {
                            len++;
                        }

                    }
                }

                if (StringUtils.isNotBlank((String) param.get("unitValueId"))) {
                    int len = 0;
                    while (len < fieldDtoList.size()) {
                        if (!param.get("unitValueId").equals(fieldDtoList.get(len).get("unitValueId"))) {
                            fieldDtoList.remove(len);
                        } else {
                            len++;
                        }

                    }
                }

                //海尔--移除为空的数据项
                if ("71800166X".equals(param.get("manufactureId"))) {
                    Set<String> set = new HashSet<>();
                    List<Map<String, Object>> dataList = (List<Map<String, Object>>) dto.get("data");
                    Map<String, Object> map2 = dataList.get(0);
                    Set<Entry<String, Object>> entrySet = map2.entrySet();
                    Iterator<Entry<String, Object>> iterator = entrySet.iterator();
                    while (iterator.hasNext()) {
                        Entry<String, Object> next = iterator.next();
                        String key = next.getKey();
                        Object value = next.getValue();
                        if (value == null) {
                            set.add(key);
                        }
                    }
                    Iterator<Map<String, Object>> iterator2 = fieldDtoList.iterator();
                    while (iterator2.hasNext()) {
                        Map<String, Object> next = iterator2.next();
                        if (set.contains(next)) {
                            iterator2.remove();
                        }
                    }
                }

                dto.put("fields", fieldDtoList);
                dto.put("classify", classify);
                Comparator<Object> com = new Comparator<Object>() {
                    @Override
                    @SuppressWarnings("unchecked")
                    public int compare(Object o1, Object o2) {
                        if (o1 instanceof Map && o2 instanceof Map) {
                            Map<String, Object> m1 = (Map<String, Object>) o1;
                            Map<String, Object> m2 = (Map<String, Object>) o2;
                            if (StrUtil.isEmpty((String) m1.get("unitId"))) {
                                return 1;
                            }
                            if (StrUtil.isEmpty((String) m2.get("unitId"))) {
                                return -1;
                            }
                            return String.valueOf(m1.get("unitId")).compareTo((String) m2.get("unitId"));
                        }
                        return 0;
                    }

                };
                Collections.sort(unitList, com);
                dto.put("units", unitList);
                res.setExtData(dto);
            }

            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
        return res;
    }

    public PageData getRealFaultList(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("查询设备信息失败");
        Connection conn = null;
        try {
            conn = DBUtils.getDBConn_Ope();
            StringBuffer sb = new StringBuffer();
            sb.append(" select a.sb_id, sbxh, sblxmc, alias, gmt_create, fault_code, seq,if(ig.id is null, 0, 1) ignored " + " from (SELECT a.sb_id,c.sbxh,c.sblxmc," + " IF ( b.alias IS NULL OR b.alias = '', c.sbxh, b.alias ) alias," + " DATE_FORMAT(a.gmt_create, '%Y-%m-%d %T') gmt_create," + " SUBSTRING_INDEX( SUBSTRING_INDEX( fault_codes, ',', seq ), ',',- 1 ) fault_code,seq " + " FROM digitssequence " + " CROSS JOIN is_norm_data a " + " INNER JOIN is_equipment_info b ON a.sb_id = b.sb_id " + " INNER JOIN t_sblx c ON b.sblx_id = c.sblx_id " + " INNER JOIN is_install_info d on b.dd_id=d.dd_id " + " WHERE fault_codes IS NOT NULL " +
                    //" AND gmt_create >= DATE_ADD( now( ), INTERVAL - 180 MINUTE ) " +
                    " ");
            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo().getAreacode(), "d.xzqh", new String[]{}));
            String companyType = req.getUinfo().getCompanyType();
            if ("01".equals(companyType)) {//厂商
                sb.append(" AND b.manufacture_id = '")
                        .append(req.getUinfo().getNes_companyId())
                        .append("' AND b.is_open = 1 ");
            } else {//代理商
                sb.append(" AND (b.agent_id = '")
                        .append(req.getUinfo().getCompanyId())
                        .append("' or b.manufacture_id = '")
                        .append(req.getUinfo().getCompanyId())
                        .append("') ");
            }
            if (!StringUtils.isEmpty((String) req.getReqData().get("sbId"))) {
                sb.append("  and b.sb_id = '").append(req.getReqData().get("sbId")).append("' ");
            }
            sb.append(" AND seq BETWEEN 1 AND ( SELECT 1 + LENGTH( fault_codes ) - LENGTH( REPLACE ( fault_codes, ',', '' ) ) ) " + " ORDER BY gmt_create DESC, a.sb_id, fault_code ) a " + " left join is_ignored_item ig on ig.sb_id = a.sb_id and ig.ignored_item = fault_code ");
            if ("0".equals(req.getReqData().get("allType"))) {
                sb.append(" where ig.id is null ");
            }
            sb.append(" order by gmt_create desc ");
            List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
        return res;
    }

    public PageData getHisFaultList(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("查询设备信息失败");
        Connection conn = null;
        try {
            conn = DBUtils.getDBConn_Ope();
            StringBuffer sb = new StringBuffer();
            StringBuffer sbfrom = new StringBuffer();
            sb.append(" select a.sb_id, sbxh, sblxmc, recoveryTime, alias, gmt_create, fault_code, seq,if(ig.id is null, 0, 1) ignored " + " from ( SELECT a.sb_id,c.sbxh,c.sblxmc," + " DATE_FORMAT(a.recoveryTime, '%Y-%m-%d %T')  recoveryTime," + " IF( b.alias IS NULL OR b.alias = '', c.sbxh, b.alias ) alias," + " DATE_FORMAT(a.cjsj, '%Y-%m-%d %T') gmt_create," + " SUBSTRING_INDEX( SUBSTRING_INDEX( a.erro_code, ',', seq ), ',',- 1 ) fault_code,seq" + " FROM digitssequence" + " CROSS JOIN is_equipment_fault_info a" + " INNER JOIN is_equipment_info b ON a.sb_id = b.sb_id" + " INNER JOIN t_sblx c ON b.sblx_id = c.sblx_id" + " INNER JOIN is_install_info d on b.dd_id=d.dd_id" + " WHERE erro_code IS NOT NULL " +
                    //" AND a.cjsj >= DATE_ADD( now( ), INTERVAL - 180 MINUTE ) " +
                    "  ");
            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo().getAreacode(), "d.xzqh", new String[]{}));
            String companyType = req.getUinfo().getCompanyType();
            if ("01".equals(companyType)) {//厂商
                sb.append(" AND b.manufacture_id = '")
                        .append(req.getUinfo().getNes_companyId())
                        .append("' AND b.is_open = 1 ");
            } else {//代理商
                sb.append(" AND (b.agent_id = '")
                        .append(req.getUinfo().getCompanyId())
                        .append("' or b.manufacture_id = '")
                        .append(req.getUinfo().getCompanyId())
                        .append("') ");
            }
            if (!StringUtils.isEmpty((String) req.getReqData().get("sbId"))) {
                sb.append("  and b.sb_id = '").append(req.getReqData().get("sbId")).append("' ");
            }
            if (!StringUtils.isEmpty((String) req.getReqData().get("startTime"))) {
                sb.append("  and a.cjsj >= '").append(req.getReqData().get("startTime")).append("' ");
            }
            if (!StringUtils.isEmpty((String) req.getReqData().get("endTime"))) {
                sb.append("  and a.cjsj <=  '").append(req.getReqData().get("endTime")).append("'  ");
            }
            sb.append(" AND seq BETWEEN 1 " + " AND ( SELECT 1 + LENGTH( a.erro_code ) - LENGTH( REPLACE ( a.erro_code, ',', '' ) ) ) " + " ORDER BY a.cjsj DESC,a.sb_id,a.erro_code ) a " + " left join is_ignored_item ig on ig.sb_id = a.sb_id and ig.ignored_item = fault_code ");
            if ("0".equals(req.getReqData().get("allType"))) {
                sb.append(" where ig.id is null ");
            }
            sbfrom.append(sb.toString());
            int idx = sbfrom.indexOf(" from ");
            sbfrom.delete(0, idx);
            Page page = new Page(conn, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, new Object[]{});
            sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
            List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block                    e.printStackTrace();
                }
            }
        }
        return res;
    }

    public PageData getRealWarns(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("查询设备信息失败");
        Connection conn = null;
        try {
            List<Map<String, Object>> warnRules = new ArrayList<>();
            conn = DBUtils.getDBConn_Ope();
            StringBuffer sb = new StringBuffer();
            sb.append(" select warn_data_items warns, gmt_create time, other_data otherData from is_norm_data  where is_warn = 1 and sb_id = '")
                    .append(req.getReqData().get("sbId"))
                    .append("' ");
            sb.append(" order by gmt_create desc limit 1 ");
            List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
            if (list.size() > 0) {
                String rawWarn = (String) list.get(0).get("warns");
                Date date = (Date) list.get(0).get("time");
                String rawData = (String) list.get(0).get("otherData");
                Map<String, String> warns = JSONObject.parseObject(rawWarn, new TypeReference<Map<String, String>>() {
                });
                if (CollUtil.isNotEmpty(warns)) {
                    List<String> validatedWarns = new ArrayList<>();
                    sb = new StringBuffer();
                    sb.append(" select ignored_item from is_ignored_item where type = 2 and sb_id = '")
                            .append(req.getReqData().get("sbId"))
                            .append("' ");
                    List<Map<String, Object>> listIng = this.queryForList(conn, sb.toString(), new Object[]{});
                    Set<String> ignoredWarns = new HashSet<String>();
                    if (listIng.size() > 0) {
                        for (int i = 0; i < listIng.size(); i++) {
                            ignoredWarns.add((String) listIng.get(i).get("ignored_item"));
                        }
                    }
                    for (Map.Entry<String, String> entry : warns.entrySet()) {
                        String warn = entry.getKey();
                        if (StrUtil.isBlank(entry.getValue()) && (req.getReqData()
                                .get("allType")
                                .toString()
                                .equals("1") || !ignoredWarns.contains(warn))) {
                            validatedWarns.add(warn);
                        }
                    }
                    //查询预警具体内容
                    if (CollUtil.isNotEmpty(validatedWarns)) {
                        warnRules = getWarnDetail(validatedWarns, rawData, conn);
                        if (CollUtil.isNotEmpty(warnRules)) {
                            warnRules.get(0).put("lastAlertTime", Globals.DATE_FORMAT_LONG.format(date));
                            warnRules.get(0).put("ignored", 1);
                        }
                    }
                }
            }
            res.setPage(warnRules);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
        return res;
    }

    public List<Map<String, Object>> getWarnDetail(List<String> validatedWarns, String rawData, Connection conn) {
        List<Map<String, Object>> warnRules = new ArrayList<>();
        try {
            if (CollUtil.isNotEmpty(validatedWarns)) {
                StringBuffer sb = new StringBuffer();
                sb.append(" select name warnName, uuid warnRuleId, json, scripts script, fldList fld" + " from is_warningrules" + " where json != '' and uuid in (null");
                for (int i = 0; i < validatedWarns.size(); i++) {
                    sb.append(",'").append(validatedWarns.get(i)).append("'");
                }
                sb.append(") ");
                warnRules = this.queryForList(conn, sb.toString(), new Object[]{});
                if (CollUtil.isNotEmpty(warnRules)) {
                    for (int i = 0; i < warnRules.size(); i++) {
                        String str = warnRules.get(i).get("json").toString();
                        JSONObject json = JSONObject.parseObject(str);
                        if (CollUtil.isNotEmpty(json)) {
                            String formula = json.getString("formula");
                            warnRules.get(0).put("formula", formula);
                            if (!StrUtil.hasBlank(formula, warnRules.get(i).get("fld").toString())) {
                                List<String> fld = StrUtil.splitTrim(warnRules.get(i).get("fld").toString(), ",");
                                if (CollUtil.isNotEmpty(fld)) {
                                    Map<String, String> variableValue = ZipUtils.getOtherDataMap(rawData, fld);
                                    if (CollUtil.isNotEmpty(variableValue)) {
                                        for (Map.Entry<String, String> entry : variableValue.entrySet()) {
                                            String f = entry.getKey();
                                            String v = entry.getValue();
                                            formula = formula.replace(f, v);
                                        }
                                        String value = MathUtils.eval(formula);
                                        warnRules.get(0).put("value", value);
                                    }
                                }
                            }
                        }
                    }
                }
            }
            return warnRules;
        } catch (Exception e) {
            return warnRules;
        }
    }

    public PageData getParamGroup(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("查询设备信息失败");
        Connection conn = null;
        try {
            conn = DBUtils.getDBConn_Ope();
            StringBuffer sb = new StringBuffer();
            sb.append(" SELECT dic_no,title from is_dict_value_info where type_id='d_type_33' and  manufacture_id=? and sfsc=0 ORDER BY dic_no ");
            List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{req.getUinfo().getNes_companyId()});
            if (list.size() == 0) {
                sb.delete(0, sb.length());
                sb.append(" SELECT dic_no,title from is_dict_value_info where type_id='d_type_33' and (manufacture_id is null or manufacture_id='') and sfsc=0 ORDER BY dic_no ");
                list = this.queryForList(conn, sb.toString(), new Object[]{});
            }
            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
        return res;
    }


    public PageData getHisWarns(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("查询设备信息失败");
        Connection conn = null;
        try {
            conn = DBUtils.getDBConn_Ope();
            StringBuffer sb = new StringBuffer();
            StringBuffer sbfrom = new StringBuffer();
            sb.append(" select b.name warnName,a.warn_id warnId,b.uuid warnRuleId,b.json,b.scripts script,a.info value," + " date_format(a.last_alert_time,'%Y-%m-%d %T') lastAlertTime,b.fldList fld,json_length(his_data) alertCount,his_data hisDataJSONStr," + " if(a.state = 0,date_format(a.xgsj,'%Y-%m-%d %T'), null) recoveryTime,if(ig.id is null, 0, 1) ignored " + " from is_equipment_warn_info a " + " inner join is_warningrules b on a.data_item_id = b.uuid " + " left join is_ignored_item ig on ig.ignored_item = b.uuid " + " where 1=1 ");
            if (!StringUtils.isEmpty((String) req.getReqData().get("sbId"))) {
                sb.append("  and b.sb_id = '").append(req.getReqData().get("sbId")).append("' ");
            }
            if (!StringUtils.isEmpty((String) req.getReqData().get("startTime"))) {
                sb.append("  and a.cjsj >= '").append(req.getReqData().get("startTime")).append("' ");
            }
            if (!StringUtils.isEmpty((String) req.getReqData().get("endTime"))) {
                sb.append("  and a.cjsj <=  '").append(req.getReqData().get("endTime")).append("'");
            }
            if (!StringUtils.isEmpty((String) req.getReqData().get("allType"))) {
                sb.append(" and ig.id is null ");
            }
            sbfrom.append(sb.toString());
            int idx = sbfrom.indexOf(" from ");
            sbfrom.delete(0, idx);
            Page page = new Page(conn, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, new Object[]{});
            sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
            List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
        return res;
    }

    public PageData getEquipmentImages(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("查询设备详情失败");
        HashMap<String, Object> reqData = req.getReqData();
        String sb_id = (String) reqData.get("sb_id");
        String dd_id = (String) reqData.get("dd_id");
        if (StrUtil.isEmpty(sb_id)) {
            res.setRetMessage("sb_id为空");
            return res;
        }
        Connection conn = null;
        try {
            conn = DBUtils.getDBConn_Ope();
            StringBuffer sb = new StringBuffer();
            sb.append("select ifnull(iiwoi.images, iii.azt) images, iii.sjly from nes_ope_prod.is_equipment_info iei ");
            sb.append(" left join nes_ope_prod.is_install_info iii on iii.dd_id = iei.dd_id ");
            sb.append("left join nes_ope_prod.is_install_work_order_info iiwoi on iiwoi.sb_id = iei.sb_id and iiwoi.dd_id = iei.dd_id and iiwoi.sfsc = '0' ");
            sb.append(" where iei.sfsc = 0 and iii.sfsc = 0 and iei.sb_id = '").append(sb_id).append("' ");
            if (StrUtil.isNotBlank(dd_id)) {
                sb.append(" and iei.dd_id = '").append(dd_id).append("'");
            }
            List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
            if (list == null || list.size() == 0) {
                res.setRetMessage("设备不存在");
            } else {
                List<String> resList = new ArrayList<>();
                String images = (String) list.get(0).get("images");
                if (StringUtils.isNotBlank(images)) {
                    if ("ctce".equals(list.get(0).get("sjly"))) {
                        for (String image : images.split(";")) {
                            resList.add(OssUtil.getImgSignURL(image, 0, bucketName_ctce));
                        }
                    } else {
                        for (String image : images.split(",")) {
                            resList.add(OssUtil.getImgSignURL(image, 0, bucketName_mtpf));
                        }
                    }
                }
                res.setData(Collections.<String, Object> singletonMap("images", resList));
                res.setRetCode(0);
                res.setRetMessage("OK");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
        return res;
    }

    public PageData checkDeviceWorkingState(DataReq req) throws Exception {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("设备巡检失败");
        Connection conn = null;
        Connection conn_ope = null;
        try {
            conn = DBUtils.getJNDIDBConn();
            conn_ope = DBUtils.getJdbcForOpe();
            String sbid = (String) req.getReqData().get("sbid");

            StringBuffer sb = new StringBuffer();
            StringBuffer result = new StringBuffer();
            sb.append("select dtu_id,sb_id,manufacture_id,device_protocol_id,fault_code,is_online,is_warn,warn_data_items,date_format(create_time, '%Y-%m-%d %T') as create_time,other_data from is_norm_data where sb_id=?");
            List<Map<String, Object>> list = this.queryForList(conn_ope, sb.toString(), new Object[]{sbid});
            if (CollUtil.isEmpty(list)) {
                res.setRetMessage("未查询到当前设备的运行数据");
                return res;
            }

            result.append("在线状态：" + (list.get(0).get("is_online").equals(0) ? "在线" : "离线"));
            result.append(" 最后采集时间：" + list.get(0).get("create_time"));
            String other_data = (String) list.get(0).get("other_data");
            Map<String, String> realDataMap = ZipUtils.getOtherDataMap(other_data);

            sb.delete(0, sb.length());
            sb.append("select c.is_plc\n" + "from is_equipment_info a\n" + "left join is_wxsb b on b.wx_id = a.wx_id\n" + "left join is_device_protocol_info c on c.protocol_id = b.protocol_id\n" + "where a.sb_id = ?");
            int isPlc = this.queryForInt(conn_ope, sb.toString(), new Object[]{sbid});

            if (list.get(0).get("is_warn").equals(1)) {
                result.append(" 告警：[");
                String warnJsonStr = (String) list.get(0).get("warn_data_items");
                JSONObject warnJson = JSONObject.parseObject(warnJsonStr);
                Set<Entry<String, Object>> entries = warnJson.entrySet();
                for (Entry<String, Object> entry : entries) {
                    //                    System.out.println("key : " + entry.getKey());
                    sb.delete(0, sb.length());
                    sb.append("select name,fldList,scripts,json from is_warningrules where uuid=?");
                    List<Map<String, Object>> list1 = this.queryForList(conn_ope, sb.toString(), new Object[]{entry.getKey()});
                    String fieldStr = (String) list1.get(0).get("fldList");
                    String scripts = (String) list1.get(0).get("scripts");
                    String json = (String) list1.get(0).get("json");
                    JSONObject jsonObject = JSONObject.parseObject(json);
                    String name = (String) list1.get(0).get("name");
                    String[] filedArr = fieldStr.split(",");
                    result.append("预警-" + name + "：");
                    if (filedArr.length > 1) {
                        String realDataScripts = StrUtil.isBlank(json) ? scripts : jsonObject.getString("formula");
                        for (String field : filedArr) {
                            String fieldName = "";
                            fieldName = getFiledName(conn_ope, sbid, sb, isPlc, field);
                            scripts = scripts.replace(field, fieldName);
                            realDataScripts = realDataScripts.replace(field, realDataMap.get(field));
                        }
                        result.append("设置值：" + scripts);
                        result.append("实际值：" + (StrUtil.isBlank(json) ? realDataScripts : JavaScriptEngine.instance()
                                .eval(realDataScripts)) + "。");

                    } else {
                        String fieldName = "";
                        String field = filedArr[0];
                        fieldName = getFiledName(conn_ope, sbid, sb, isPlc, field);
                        result.append("设置值：" + scripts.replace(field, fieldName));
                        result.append("实际值：" + realDataMap.get(field) + "。");
                    }

                }
                result.append("]");
            }
            result.append(" 故障描述：" + (StrUtil.isEmpty((CharSequence) list.get(0)
                    .get("fault_code")) ? "无故障" : list.get(0).get("fault_code")));


            Map<String, Object> data = new HashMap<>();
            data.put("result", result.toString());
            res.setData(data);
            res.setRetCode(0);
            res.setRetMessage("ok");
        } finally {
            if (conn != null) {
                conn.close();
            }
            if (conn_ope != null) {
                conn_ope.close();
            }
        }
        return res;
    }

    private String getFiledName(Connection conn_ope, String sbid, StringBuffer sb, int isPlc,
            String field) throws SQLException {
        String fieldName;
        if (isPlc == 1) {
            sb.delete(0, sb.length());
            sb.append("select a.name\n" + "from is_device_protocol_data_item_info a\n" + "left join is_data_item_template_info b on a.template_id = b.template_id and b.is_deleted=0\n" + "left join is_device_protocol_info c on c.protocol_id = a.protocol_id and c.is_deleted=0\n" + "left join is_wxsb d on d.protocol_id = c.protocol_id and d.sfsc =0\n" + "left join is_equipment_info e on e.wx_id = d.wx_id and e.sfsc = 0\n" + "left join is_device_item f on a.data_item_id = f.item_id and e.sb_id = f.sb_id and f.is_deleted = 0\n" + "where a.is_deleted = 0 and b.field_name =? and f.sb_id = ?");
            List<Map<String, Object>> list2 = this.queryForList(conn_ope, sb.toString(), new Object[]{field, sbid});
            fieldName = (String) list2.get(0).get("name");
        } else {
            sb.delete(0, sb.length());
            sb.append("select a.name\n" + "from is_device_protocol_data_item_info a\n" + "left join is_data_item_template_info b on a.template_id = b.template_id and b.is_deleted=0\n" + "left join is_device_protocol_info c on c.protocol_id = a.protocol_id and c.is_deleted=0\n" + "left join is_wxsb d on d.protocol_id = c.protocol_id and d.sfsc =0\n" + "left join is_equipment_info e on e.wx_id = d.wx_id and e.sfsc = 0\n" + "where a.is_deleted = 0 and b.field_name =? and e.sb_id = ?");
            List<Map<String, Object>> list2 = this.queryForList(conn_ope, sb.toString(), new Object[]{field, sbid});
            fieldName = (String) list2.get(0).get("name");
        }
        return fieldName;
    }

    public PageData sendControlCommand(DataReq req) throws Exception {
        PageData res = new PageData();
        JwtUtil jwtUtil = new JwtUtil();
        res.setRetCode(9);
        res.setRetMessage("控制失败");
        List<CommandParams> params = (List<CommandParams>) req.getReqData().get("params");
        String userId = (String) req.getReqData().get("userid");
        String from = (String) req.getReqData().get("from");
        String terminal = (String) req.getReqData().get("terminal");
        HashMap<String, Byte> terminalMap = new HashMap<>();
        terminalMap.put("pc", (byte) 0);
        terminalMap.put("工程端", (byte) 1);
        terminalMap.put("用户端", (byte) 2);
        terminalMap.put("mtpf", (byte) 3);
        HashMap<String, Object> userInfoMap = new HashMap<>();
        userInfoMap.put("userid", StrUtil.isEmpty(userId) ? req.getUinfo().getId() : userId);
        userInfoMap.put("deadline", 60 * 1000L);
        userInfoMap.put("from", StrUtil.isEmpty(from) ? "agent" : from);
        userInfoMap.put("ip", req.getReqData().get("remoteIp"));
        userInfoMap.put("terminal", StrUtil.isEmpty(terminal) ? 9 : terminalMap.get(terminal));
        Map<String, Object> jsonMap = new HashMap<>(2);
        jsonMap.put("params", params);
        jsonMap.put("userinfo", userInfoMap);
        String jwt = jwtUtil.createJWT((String) userInfoMap.get("userid"), (new JSONObject(jsonMap)).toJSONString(), 60 * 1000L);
        Map<String, Object> param = new HashMap<>(1);
        param.put("params", jwt);
        //生成校验key
        LinkedHashMap<String, String> keyMap = new LinkedHashMap<String, String>();
        keyMap.put("phone", "15501181001");
        keyMap.put("code", "1111");
        keyMap.put("time", String.valueOf(System.currentTimeMillis()));
        String token = jwtUtil.createJWT("15501181001" + "_" + keyMap.get("time"), JSON.toJSONString(keyMap), 60 * 60 * 1000);
        param.put("token", token);
        param.put("async", 0);
        JSONObject p = new JSONObject(param);
        String url = OpeMethodEnum.sendControlCommandNoEncryption.url();
        Map<String, String> headers = new HashMap<>();
        headers.put("key", req.getKey());
        String s = HttpUtils.sendPost(url, new JSONObject(param).toJSONString().getBytes(), headers);
        JSONObject result = JSONObject.parseObject(s);
        if (result.getInteger("code") != 0) {
            res.setRetMessage(result.getString("msg"));
            return res;
        }
        res.setRetCode(0);
        res.setRetMessage("ok");
        return res;
    }

    public PageData getControlLog(DataReq req) throws Exception {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("控制日志查询失败");
        Connection conn = null;
        StringBuffer sb = new StringBuffer();
        try {
            conn = DBUtils.getJNDIDBConn();
            String dtuId = (String) req.getReqData().get("dtuId");  // dtu号
            String sbid = (String) req.getReqData().get("sbid");    // 设备ID
            String xzqhdm = (String) req.getReqData().get("xzqhdm");    // 行政区划代码
            String functionCodeName = (String) req.getReqData().get("functionCodeName");    // 功能码名称
            String devname = (String) req.getReqData().get("devname");    // 设备名称
            String userId = req.getUinfo().getId();
            sb.append("select l.dtu_id, l.sb_id, l.record_time, l.function_codeName, l.control_value, l.result, inet_ntoa(l.ipv4) as ip, idpi.name as protocolName,\n" + "       (select t.username from t_user t where t.id = l.user_id ) as username,\n" + "       replace(ipcc.name, concat(iei.alias, '-'),'') as controlCommandName,\n" + "       if(iei.alias is null or iei.alias = '', if(ts.sbmc is null , ts.sblxmc, ts.sbmc), iei.alias) as deviceName\n" + " from nes_ope_prod.control_send_log l\n" + " inner join nes_ope_prod.is_wxsb w on w.wx_id = l.dtu_id and w.sfsc = 0\n" + " inner join nes_ope_prod.is_device_protocol_info idpi on idpi.protocol_id = w.protocol_id and idpi.is_deleted = 0\n" + " inner join nes_ope_prod.is_protocol_control_command ipcc on ipcc.control_command_id = l.command_id and ipcc.is_deleted = 0\n" + " inner join nes_ope_prod.is_equipment_info iei on iei.sb_id = l.sb_id and iei.sfsc = 0\n" + " inner join nes_ope_prod.is_manufacture_info imi  on imi.manufacture_id = iei.manufacture_id\n" + "  inner join nes_ope_prod.is_install_info iii on iii.dd_id = iei.dd_id and iii.sfsc = 0\n" + " inner join nes_ope_prod.t_sblx ts on ts.sblx_id = iei.sblx_id\n");
            sb.append(" where  ");
            String companyType = req.getUinfo().getCompanyType();
            if (!Globals.DEMO_COMPANYID.equals(req.getUinfo().getCompanyId())) {//不是演示账号
                if ("01".equals(companyType)) {//厂商
                    sb.append("  iei.manufacture_id= '").append(req.getUinfo().getNes_companyId()).append("' ");
                } else if ("02".equals(companyType)) {//代理商
                    sb.append("  iei.agent_id= '").append(req.getUinfo().getCompanyId()).append("' ");
                } else {//管理员
                    sb.append("  iei.manufacture_id='MA3NU7QX1' ");
                }
            } else {//演示账号默认德尚
                sb.append("  iei.manufacture_id='MA3NU7QX1' ");
            }

            sb.append(StrUtil.isNotEmpty(dtuId) ? " and l.dtu_id = '" + dtuId + "'" : "");
            sb.append(StrUtil.isNotEmpty(sbid) ? " and l.sb_id = '" + sbid + "'" : "");
            sb.append(StrUtil.isNotEmpty(functionCodeName) ? " and l.function_codeName like concat('%','" + functionCodeName + "','%')" : "");
            sb.append(StrUtil.isNotEmpty(devname) ? "and (iei.alias like concat('%', '" + devname + "', '%') or ts.sbmc like concat('%', '" + devname + "', '%') or ts.sblxmc like concat('%', '" + devname + "', '%')  or iii.xmlxr like concat('%', '" + devname + "', '%') or iii.xmlxrdh like concat('%', '" + devname + "', '%'))" : "");
            StringBuffer sbfrom = new StringBuffer();
            sbfrom.append(sb.toString());
            int idx = sbfrom.lastIndexOf(" from ");
            sbfrom.delete(0, idx);
            Page page = new Page(conn, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, new Object[]{});
            sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
            List<Map<String, Object>> list = queryForList(conn, sb.toString(), new Object[]{});

            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
        return res;
    }

    public PageData editEquipmentInfo(DataReq req) throws Exception {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("编辑设备信息失败");
        Connection conn = null;
        StringBuffer sb = new StringBuffer();
        try {
            conn = DBUtils.getJdbcForOpe();

            String sbid = (String) req.getReqData().get("sbid");    // 设备ID
            // 安装信息
            String name = (String) req.getReqData().get("name");    // 安装人姓名
            String phone = (String) req.getReqData().get("phone");  // 安装人手机号
            String xzqhdm = (String) req.getReqData().get("xzqhdm");    //安装所在区域
            String installDate = (String) req.getReqData().get("installDate"); // 安装日期
            String zbrq = (String) req.getReqData().get("zbrq"); // 质保日期

            // 设备信息
            String alias = (String) req.getReqData().get("alias"); // 设备名称
            String sbxh = (String) req.getReqData().get("sbxh"); // 设备型号
            String sbxlh = (String) req.getReqData().get("sbxlh"); // 设备序列号

            // 保存安装信息
            sb.append("select dd_id from is_equipment_info where sb_id = ?");
            List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{sbid});
            if (ObjectUtil.isNotEmpty(list)) {
                String dd_id = (String) list.get(0).get("dd_id");
                sb.delete(0, sb.length());
                sb.append("update is_install_info set xmlxr=?, xmlxrdh=?,xzqh=?,cjsj=?, zbrq=?");
                sb.append(" where dd_id = ?");
                this.update(conn, sb.toString(), new Object[]{name, phone, xzqhdm, installDate, zbrq, dd_id});
            }

            // 保存设备信息
            sb.delete(0, sb.length());
            sb.append("update is_equipment_info set alias=?, sblx_id=?, sbxlh=? ");
            sb.append(" where sb_id = ?");
            this.update(conn, sb.toString(), new Object[]{alias, sbxh, sbxlh, sbid});


            conn.commit();
            res.setRetCode(0);
            res.setRetMessage("ok");
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
        return res;
    }

    public PageData updateEquipment(DataReq req) throws SQLException {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("编辑设备信息失败");
        Connection conn = null;
        StringBuffer sb = new StringBuffer();
        try {
            conn = DBUtils.getJdbcForOpe();
            //修改设备
            String ddId = (String) req.getReqData().get("ddId");
            String zbrq = (String) req.getReqData().get("zbrq");
            String manufactureId = req.getUinfo().getNes_companyId();
            BigDecimal jd = (BigDecimal) req.getReqData().get("jd");
            BigDecimal wd = (BigDecimal) req.getReqData().get("wd");
            String name = (String) req.getReqData().get("name");
            String phone = (String) req.getReqData().get("phone");
            String address = (String) req.getReqData().get("address");
            String xzqhdm = (String) req.getReqData().get("xzqhdm");
            //            int id = (int) req.getReqData().get("id");
            //            String sjly = (String) req.getReqData().get("sjly");
            //            String sbId = (String) req.getReqData().get("sbId");
            //            String sbxlh = (String) req.getReqData().get("sbxlh");
            //            String wxId = (String) req.getReqData().get("wxId");
            //            String sblxId = (String) req.getReqData().get("sblxId");
            //            String alias = (String) req.getReqData().get("alias");
            //            String protocolId = (String) req.getReqData().get("protocolId");
            //            String dealerName = (String) req.getReqData().get("dealerName");
            //            String dealerPhone = (String) req.getReqData().get("dealerPhone");
            //            String createOper = (String) req.getReqData().get("create_oper");
            //            String manufactureId = (String) req.getReqData().get("manufactureId");
            //            sb.append("UPDATE nes_ope_prod.is_equipment_info SET sjly = ?,sb_id = ?,sbxlh = ?,wx_id = ?,\n" +
            //                              "dd_id = ?,sblx_id = ?,alias = ?,zbq = ?,protocol_id = ?,dealer_name = ?,dealer_phone = ?,create_oper = ?,manufacture_id = ? WHERE id =?");
            if (StringUtils.isNotEmpty(ddId)) {
                sb.append("update nes_ope_prod.is_install_info set ");
                if (jd != null) {
                    sb.append("jd = " + jd + ",");
                }
                if (wd != null) {
                    sb.append("wd = " + wd + ",");
                }
                if (StringUtils.isNotBlank(name)) {
                    sb.append("xmlxr = '" + name + "',");
                }
                if (StringUtils.isNotBlank(phone)) {
                    sb.append("xmlxrdh= '" + phone + "',");
                }
                if (StringUtils.isNotBlank(xzqhdm)) {
                    sb.append("xzqh = '" + xzqhdm + "',");
                }
                if (StringUtils.isNotBlank(address)) {
                    sb.append("xmdz = '" + address + "',");
                }
                if (sb != null && sb.length() > 0 && sb.charAt(sb.length() - 1) == ',') {
                    sb.deleteCharAt(sb.length() - 1);
                }

                sb.append(" where dd_id = '" + ddId + "'");
                this.update(conn, sb.toString(), new Object[]{});
                conn.commit();
                if (StringUtils.isNotBlank(zbrq)) {
                    sb.append(" UPDATE nes_ope_prod.is_equipment_info a SET a.zbq = '" + zbrq + "', a.xgsj = NOW() WHERE a.dd_id = '" + ddId + "' AND a.sfsc = 0 AND a.`manufacture_id` = '" + manufactureId + "'");
                    conn.commit();
                }
            }
            res = PageData.ok();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
        return res;
    }

    public PageData deleteEquipment(DataReq req) throws SQLException {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("编辑设备信息失败");
        Connection conn = null;
        StringBuffer sb = new StringBuffer();
        try {
            conn = DBUtils.getJdbcForOpe();
            sb.append("UPDATE nes_ope_prod.is_equipment_info a SET a.sfsc = 1,a.xgsj = NOW() ");
            sb.append(" WHERE a.sb_id = ? AND a.sfsc = 0 AND a.`manufacture_id` = ?");
            String sbId = (String) req.getReqData().get("sbId");
            String manufactureId = (String) req.getReqData().get("manufactureId");
            int i = this.update(conn, sb.toString(), new Object[]{sbId, manufactureId});
            conn.commit();
            res = PageData.ok();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
        return res;
    }

    @Transactional(rollbackFor = Exception.class)
    public PageData equipmentUntying(String sbId) throws SQLException {
        //解绑来源（固定）
        String sjly = "ctce";
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("编辑设备信息失败");
        Connection conn = null;
        StringBuffer sb = new StringBuffer();
        try {
            conn = DBUtils.getJdbcForOpe();
            //机组PLC
            Short isPlc = 1;

            IsEquipmentInfo equipmentInfo = equipmentMapper.getEquipmentInfoBySbId(sbId);
            String wxId = equipmentInfo.getWxId();
            if (StrUtil.isEmpty(wxId)) {
                return PageData.error("该设备未绑定DTU");
            }
            //解绑设备为单机设备
            if (ObjectUtil.isNull(equipmentInfo.getIsMaster())) {
                // 删除设备绑定
                int i1 = equipmentMapper.deleteBySbId(sbId);
                // 删除定时任务
                int i2 = equipmentMapper.deleteTimerBySbId(sbId, sjly);
                //删除无线设备的绑定设备id
                Integer updateRes = equipmentMapper.updateTimeStampByWxId(wxId, sjly);
                if (updateRes != 1) {
                    return PageData.error("保存失败");
                }
                // 修改设备状态
                int i = equipmentMapper.updateUntyingStatusBySbId(sbId, sjly);
                if (i < 1) {
                    return PageData.error("修改失败");
                }
                return PageData.ok();
            }
            //解绑设备机组非PLC设备
            else if (!equipmentInfo.getIsMaster().equals(isPlc)) {
                //查询该设备所属机组下的所有设备
                List<IsEquipmentInfo> equipments = equipmentMapper.selectByExample(equipmentInfo.getSbPid());
                //获取机组PLC设备信息
                IsEquipmentInfo plc = equipmentMapper.getEquipmentInfoBySbId(equipmentInfo.getSbPid());
                if (plc != null) {
                    equipments.add(plc);
                }
                //机组设备为所属机组下的所有设备绑定DTU
                for (IsEquipmentInfo equipment : equipments) {
                    equipment.setWxId("");
                    equipment.setBdzt(0);
                    equipment.setXgsj(null);
                    equipment.setSjly(sjly);
                    int x = equipmentMapper.updateByPrimaryKeySelective(equipment);
                    if (x != 1) {
                        //机组中任意一台设备绑定DTU失败,数据回滚
                        return PageData.error("绑定DTU失败");
                    }
                }
                //删除无线设备的绑定设备id
                Integer updateRes = equipmentMapper.updateTimeStampByWxId(wxId, sjly);
                if (updateRes != 1) {
                    return PageData.error("保存失败");
                }
                return PageData.ok();
            }
            //解绑设备为机组PLC
            else {
                //查询该设备所属机组下的所有设备
                List<IsEquipmentInfo> equipments = equipmentMapper.selectByExample(equipmentInfo.getSbPid());
                //获取机组PLC设备信息
                IsEquipmentInfo plc = equipmentMapper.getEquipmentInfoBySbId(equipmentInfo.getSbId());
                if (plc != null) {
                    equipments.add(plc);
                }
                //为所属机组下的所有设备解绑DTU
                for (IsEquipmentInfo equipment : equipments) {
                    equipment.setWxId("");
                    equipment.setBdzt(0);
                    equipment.setXgsj(null);
                    equipment.setSjly(sjly);
                    int x = equipmentMapper.updateByPrimaryKeySelective(equipment);
                    if (x != 1) {
                        //机组中任意一台设备绑定DTU失败,数据回滚
                        return PageData.error("绑定DTU失败");
                    }
                }
                //删除无线设备的绑定设备id
                Integer updateRes = equipmentMapper.updateTimeStampByWxId(wxId, sjly);
                if (updateRes != 1) {
                    return PageData.error("保存失败");
                }
                return PageData.ok();
            }
        } catch (Exception e) {
            e.printStackTrace();
            return res;
        } finally {
            if (conn != null) {
                conn.close();
            }
        }

    }

    public int verifyUntyingSecret(String secret, DataReq req) {
        String manufactureId = req.getUinfo().getNes_companyId();
        String manufactureSecret = equipmentMapper.getManufactureSecret(manufactureId);
        return StrUtil.isBlank(manufactureSecret) ? 0 : manufactureSecret.compareTo(secret);
    }

    public PageData unbindEquipment(DataReq req) throws Exception {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("解绑设备失败");
        Connection conn = null;
        StringBuffer sb = new StringBuffer();
        try {
            conn = DBUtils.getJdbcForOpe();
            String sbid = (String) req.getReqData().get("sbId");    // 设备ID

            String wxid = "";
            sb.delete(0, sb.length());
            sb.append("select wx_id from is_equipment_info where sb_id=?");
            List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{sbid});
            if (list.size() == 0) {
                res.setRetCode(9);
                res.setRetMessage("解绑设备失败");
                return res;
            } else {
                wxid = (String) list.get(0).get("wx_id");
            }

            if (StringUtils.isBlank(wxid)) {
                res.setRetCode(9);
                res.setRetMessage("解绑设备失败");
                return res;
            }

            // 保存设备信息
            sb.delete(0, sb.length());
            sb.append("update is_equipment_info SET wx_id=null, bdzt=0, sjly ='mtpf' WHERE sb_id =? ");
            int r = this.update(conn, sb.toString(), new Object[]{sbid});
            if (r < 1) {
                res.setRetCode(9);
                res.setRetMessage("解绑设备失败");
                return res;
            }

            sb.delete(0, sb.length());
            sb.append("update is_wxsb SET xgsj= now(), version = UNIX_TIMESTAMP(now(3)) * 1000,sjly ='mtpf' WHERE  wx_id =? ");
            r = this.update(conn, sb.toString(), new Object[]{wxid});

            if (r < 1) {
                res.setRetCode(9);
                res.setRetMessage("解绑设备失败");
                return res;
            }

            conn.commit();
            res.setRetCode(0);
            res.setRetMessage("ok");
        } catch (Exception e) {
            e.printStackTrace();
            res.setRetCode(9);
            res.setRetMessage("解绑设备失败");
        } finally {
            if (conn != null) {
                conn.rollback();
                conn.close();
            }
        }
        return res;
    }

}
